Partition statistics or global statistics

I discovered an interesting thing about partition statistics and global statistics today. If global statistics have never been gathered for a table, then after all partitions have been analyzed, Oracle will update the table level statistics automatically.

However if global statistics have been gathered then analyzing an individual partition will not update the table level statistics.

create table t1 with 3 partitions

jeff@ORA10GR2> create table t1
2 (username varchar2(30) not null
3 ,created date not null)
4 partition by range (created)
5 (partition p1 values less than (to_date('20060201','YYYYMMDD'))
6 ,partition p2 values less than (to_date('20060301','YYYYMMDD'))
7 ,partition p3 values less than (to_date('20060401','YYYYMMDD'))
8 );

Table created.


insert data into all 3 partitions

jeff@ORA10GR2> insert into t1
2 select username, add_months(to_date('20060301','YYYYMMDD'), (mod(rownum, 3) * - 1))
3 from all_users;

35 rows created.


show the count from t1

jeff@ORA10GR2> select count(*) from t1;

COUNT(*)
----------
35


No statistics have been gathered yet

jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 NO
P2 NO
P3 NO

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 NO


analyze partition p1

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P1', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

now the p1 partition has statistics

jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 12 28/FEB/06
P2 NO
P3 NO

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 NO


analyze partition p2

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P2', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

now partition p1 and p2 have statistics

jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 12 28/FEB/06
P2 YES 12 28/FEB/06
P3 NO

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 NO

analyze partition p3

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P3', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

now all partition have statistics and there are table level statistics

jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 12 28/FEB/06
P2 YES 12 28/FEB/06
P3 YES 11 28/FEB/06

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 NO 35 28/FEB/06

insert some more data into the table t1

jeff@ORA10GR2> insert into t1
2 select username, add_months(to_date('20060301','YYYYMMDD'), (mod(rownum, 3) * - 1))
3 from all_users;

35 rows created.

show the new count from table t1

jeff@ORA10GR2> select count(*) from t1;

COUNT(*)
----------
70

analyze partition p1

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P1', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

Now the partition p1 statistics are updated
and the table level statistics are updated for p1 only

this shows how Oracle totals all of the current partition level statistics
when all partitions are anlayzed and global statistics have not been gathered

jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 24 28/FEB/06
P2 YES 12 28/FEB/06
P3 YES 11 28/FEB/06

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 NO 47 28/FEB/06

analyze partition p2 and both statistics get updated

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P2', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

jeff@ORA10GR2>
jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 24 28/FEB/06
P2 YES 24 28/FEB/06
P3 YES 11 28/FEB/06

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 NO 59 28/FEB/06

analyze partition p3 and both statistics get updated

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P3', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

jeff@ORA10GR2>
jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 24 28/FEB/06
P2 YES 24 28/FEB/06
P3 YES 22 28/FEB/06

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 NO 70 28/FEB/06

Now gather global statistics

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1' );

PL/SQL procedure successfully completed.

Now global stats have been set

jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 24 28/FEB/06
P2 YES 24 28/FEB/06
P3 YES 22 28/FEB/06

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 YES 70 28/FEB/06

insert some more data into the table t1

jeff@ORA10GR2> insert into t1
2 select username, add_months(to_date('20060301','YYYYMMDD'), (mod(rownum, 3) * - 1))
3 from all_users;

35 rows created.

analyze all 3 partitions

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P1', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P2', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

jeff@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1', 'P3', granularity=>'PARTITION' );

PL/SQL procedure successfully completed.

Now the table level statistics are not updated

jeff@ORA10GR2> select partition_name, global_stats, num_rows, last_analyzed from user_tab_partitions where table_name = 'T1';

PARTITION_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
P1 YES 36 28/FEB/06
P2 YES 36 28/FEB/06
P3 YES 33 28/FEB/06

jeff@ORA10GR2> select table_name, global_stats, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME GLO NUM_ROWS LAST_ANAL
------------------------------ --- ---------- ---------
T1 YES 70 28/FEB/06

jeff@ORA10GR2>
jeff@ORA10GR2> spool off