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

