create table DEMO (day date) partition by range(day) ( partition P2000 values less than (date '2001-01-01'), partition P2001 values less than (date '2002-01-01'), partition P2002 values less than (date '2003-01-01'), partition P2003 values less than (date '2004-01-01'), partition P2004 values less than (date '2005-01-01'), partition P2005 values less than (date '2006-01-01'), partition INFINITY values less than (MAXVALUE) ); -- by default GRANULARITY=AUTO and INCREMENTAL=FALSE -- on this table overwrite property GRANULARITY exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'GRANULARITY', pvalue=>'PARTITION'); -- insert some lines in the first 4 partitions insert into DEMO select date '2000-01-01'+rownum from xmltable('1 to 1460'); commit; -- gather stats on the first 5 partitions exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'DEMO',partname=>'P2000'); exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'DEMO',partname=>'P2001'); exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'DEMO',partname=>'P2002'); exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'DEMO',partname=>'P2003'); exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'DEMO',partname=>'P2004'); -- show table stats set lines 256 col OWNER for a20 col TABLE_NAME for a20 col PARTITION_NAME for a20 col LAST_ANALYZED for a20 col GLOBAL_STATS for a3 col STALE_STATS for a5 select OWNER,TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED,GLOBAL_STATS,STALE_STATS from dba_tab_statistics where table_name='DEMO' order by PARTITION_POSITION asc; OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE -------------------- -------------------- -------------------- ---------- -------------------- --- ----- POC DEMO P2000 365 21-JAN-24 YES NO POC DEMO P2001 365 21-JAN-24 YES NO POC DEMO P2002 365 21-JAN-24 YES NO POC DEMO P2003 365 21-JAN-24 YES NO POC DEMO P2004 0 21-JAN-24 YES NO POC DEMO P2005 NO POC DEMO INFINITY NO POC DEMO NO -- as expected, 365 lines in the first 4 partitions, 0 lines in ther 5-th partition and no stats on other partitions -- also, no global stats at table level -- now set INCREMENTAL property to TRUE for the table exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'INCREMENTAL', pvalue=>'TRUE'); -- get statistics on P2005 exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'DEMO',partname=>'P2005'); -- still no global stats, because no stats last partition OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE -------------------- -------------------- -------------------- ---------- -------------------- --- ----- POC DEMO P2000 365 21-JAN-24 YES NO POC DEMO P2001 365 21-JAN-24 YES NO POC DEMO P2002 365 21-JAN-24 YES NO POC DEMO P2003 365 21-JAN-24 YES NO POC DEMO P2004 0 21-JAN-24 YES NO POC DEMO P2005 0 21-JAN-24 YES NO POC DEMO INFINITY NO POC DEMO NO -- get statistics on the last partition exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'DEMO',partname=>'INFINITY'); -- now we can see global stats at the table level -- note that GLOBAL_STATS=NO and STALE_STATS=YES for the global (aggregate) table stats OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE -------------------- -------------------- -------------------- ---------- -------------------- --- ----- POC DEMO P2000 365 21-JAN-24 YES NO POC DEMO P2001 365 21-JAN-24 YES NO POC DEMO P2002 365 21-JAN-24 YES NO POC DEMO P2003 365 21-JAN-24 YES NO POC DEMO P2004 0 21-JAN-24 YES NO POC DEMO P2005 0 21-JAN-24 YES NO POC DEMO INFINITY 0 21-JAN-24 YES NO POC DEMO 1460 21-JAN-24 NO YES -- now delete table stats and use gather_table_stats to gather stats on the whole table exec dbms_stats.delete_table_stats(user,'DEMO'); exec dbms_stats.gather_table_stats(user,'DEMO'); -- the result is identical as getting stats individually for each partition OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE -------------------- -------------------- -------------------- ---------- -------------------- --- ----- POC DEMO P2000 365 21-JAN-24 YES NO POC DEMO P2001 365 21-JAN-24 YES NO POC DEMO P2002 365 21-JAN-24 YES NO POC DEMO P2003 365 21-JAN-24 YES NO POC DEMO P2004 0 21-JAN-24 YES NO POC DEMO P2005 0 21-JAN-24 YES NO POC DEMO INFINITY 0 21-JAN-24 YES NO POC DEMO 1460 21-JAN-24 NO YES -- reset table prefs to default values exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'GRANULARITY', pvalue=>'AUTO'); exec DBMS_STATS.SET_TABLE_PREFS(ownname=>'POC',tabname=>'DEMO', pname=>'INCREMENTAL', pvalue=>'FALSE'); -- delete and gather table stats exec dbms_stats.delete_table_stats(user,'DEMO'); exec dbms_stats.gather_table_stats(user,'DEMO'); -- note that GLOBAL_STATS=YES and STALE_STATS=NO for the global (aggregate) table stats OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED GLO STALE -------------------- -------------------- -------------------- ---------- -------------------- --- ----- POC DEMO P2000 365 21-JAN-24 YES NO POC DEMO P2001 365 21-JAN-24 YES NO POC DEMO P2002 365 21-JAN-24 YES NO POC DEMO P2003 365 21-JAN-24 YES NO POC DEMO P2004 0 21-JAN-24 YES NO POC DEMO P2005 0 21-JAN-24 YES NO POC DEMO INFINITY 0 21-JAN-24 YES NO POC DEMO 1460 21-JAN-24 YES NO -- CONCLUSION: when INCREMENTAL stats are activated: - ALL partitions stats are required in order to estimate the GLOBAL table stats - in DBA_TAB_STATISTICS, columns GLOBAL_STATS=NO and STALE_STATS=YES for the aggregated table stats