Gather empty schema statistics might cause index statistics to be marked as stale.

Firstly, we will create a table with an index:

drop user u cascade ;
create user u identified by Temp_1234 ;
create table u.test ( a varchar2(100) ) ;
CREATE INDEX u.ind_test_a ON U.TEST (a);

As expected, there are no table statistics after table creation:

select stale_stats,last_analyzed 
  from dba_tab_statistics 
  where owner='U' and table_name='TEST' 
;
STAL LAST_ANALY
---- ----------
NULL NULL

Although the statistics for the index were gathered during the index creation, the stale_stats is NULL:

select stale_stats,last_analyzed 
  from dba_ind_statistics 
  where table_owner='U' and table_name='TEST' 
;
STAL LAST_ANALYZED
---- -------------------
NULL 25.10.2015 21:09:30

Let’s gather empty schema statistics:

begin
  SYS.DBMS_STATS.GATHER_schema_STATS (
    OwnName => 'U' ,
    Options => 'GATHER EMPTY' ,
    Cascade => TRUE
  );
end ;
/

The table statistics are updated:

select stale_stats,last_analyzed 
  from dba_tab_statistics 
  where owner='U' and table_name='TEST' 
;
STAL LAST_ANALYZED
---- -------------------
NO   25.10.2015 21:09:31

However, according to LAST_ANALYZED the index statistics were not updated, but are marked as stale now:

select stale_stats,last_analyzed 
  from dba_ind_statistics 
  where table_owner='U' and table_name='TEST' 
;
STAL LAST_ANALYZED
---- -------------------
YES  25.10.2015 21:09:30

Partitioned index

I discovered another variant of this problem when a local index is involved.

Creating the model:

drop table t ;

create table t ( n1 integer, n2 integer ) partition by range (n1)
(
  partition t_1 values less than (1) 
);

insert into t values (0,0) ; 

commit ;

create index ix_t_1 on t (n2) local ;

Adjusting column formatting:

column partition_name format a4
column stale_stats format a4
column index_name format a6

Gathering statistics:

exec dbms_stats.gather_table_stats( null, 'T' ) ;
select index_name, partition_name, stale_stats, last_analyzed 
  from user_ind_statistics where table_name = 'T' ;

INDEX_ PART STAL LAST_ANALYZED
------ ---- ---- -------------------
IX_T_1      NO   2020-12-04 18:23:05
IX_T_1 T_1  NO   2020-12-04 18:23:05

Creating a new partition:

alter table t add partition t_2 values less than (2) ;

INDEX_ PART STAL LAST_ANALYZED
------ ---- ---- -------------------
IX_T_1      NO   2020-12-04 18:23:05
IX_T_1 T_1  NO   2020-12-04 18:23:05
IX_T_1 T_2

Index partition rebuild gathers statistics, but doesn’t populate the STALE_STATS column.

alter index ix_t_1 rebuild partition t_2 online ;

INDEX_ PART STAL LAST_ANALYZED
------ ---- ---- -------------------
IX_T_1      NO   2020-12-04 18:23:05
IX_T_1 T_1  NO   2020-12-04 18:23:05
IX_T_1 T_2       2020-12-04 18:24:17

Gather empty schema statistics gathers the partition statistics, including the index statistics, but wrongly sets the STALE_STATS column to ‘YES’.

begin
    SYS.DBMS_STATS.GATHER_schema_STATS (
    null,
    Options => 'GATHER EMPTY' ,
    Cascade => TRUE
  );
end ;
/

INDEX_ PART STAL LAST_ANALYZED
------ ---- ---- -------------------
IX_T_1      NO   2020-12-04 18:23:05
IX_T_1 T_1  NO   2020-12-04 18:23:05
IX_T_1 T_2  YES  2020-12-04 18:24:17

Conclusion

In conclusion, implicit index statistics gathering through, for example index creation or rebuild, doesn’t set STALE_STATS. A subsequent gather empty schema statistics then wrongly sets the index STALE_STATS to ‘YES’.

Updates

• January 14, 2016 – Oracle filed the Bug 22544615 : GATHER EMPTY OPTION OF GATHERING STATS MARKS THE INDEX STALE
• December 4, 2020 – the local index case and conclusion added