134 lines
8.3 KiB
Plaintext
134 lines
8.3 KiB
Plaintext
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
|
|
|