Here is an example of how to collect partitioned table stats. Specifically for Non-LIST sub-partition tables, whose sub-partition stats will not be collected by default. here is the rule :
When the AUTO option is specified for “granularity”, Oracle collects global, partition-level, and sub-partition level statistics if sub-partition method is LIST. For other partitioned tables, only the global and partition level statistics are generated.
1)Create a sample range type sub-partition table.
CREATE TABLE STUDENTS
(
ENROLLMENT_DATE DATE NOT NULL,
DEPARTMENT_ID NUMBER(2) NOT NULL,
STUDENT_ID NUMBER(6)
)
PARTITION BY RANGE (ENROLLMENT_DATE)
SUBPARTITION BY RANGE (DEPARTMENT_ID)
SUBPARTITION TEMPLATE
(SUBPARTITION SP_1 VALUES LESS THAN (10),
SUBPARTITION SP_2 VALUES LESS THAN (20),
SUBPARTITION SP_MAX VALUES LESS THAN (MAXVALUE)
)
(
PARTITION P_2016 VALUES LESS THAN (TO_DATE('2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_2017 VALUES LESS THAN (TO_DATE('2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);
Table created.
insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 8,1);
insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 12,2);
insert into students values (TO_DATE('2016-07-01 10:20:08', 'YYYY-MM-DD HH24:MI:SS'), 25,3);
insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 7,4);
insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 13,5);
insert into students values (TO_DATE('2017-02-09 18:34:43', 'YYYY-MM-DD HH24:MI:SS'), 23,6);
insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 3,7);
insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 19,8);
insert into students values (TO_DATE('2018-08-03 02:30:45', 'YYYY-MM-DD HH24:MI:SS'), 28,9);
2) Check table, partition and sub-partition without stats:
SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED
---------- ----------------- ---------- -------------
STUDENTS P_2016
STUDENTS P_2017
STUDENTS P_MAX
SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
from user_tables
where table_name='STUDENTS';
TABLE_NAME NUM_ROWS LAST_ANALYZED
----------- ---------- ---------------
STUDENTS
SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,NUM_ROWS,
LAST_ANALYZED
from user_tab_subpartitions
where table_name='STUDENTS'
order by PARTITION_NAME,LAST_ANALYZED;
TABLE_NAME PARTITIO SUBPARTITION_NAME NUM_ROWS LAST_ANAL
---------- -------- ----------------- -------- ----------
STUDENTS P_2016 P_2016_SP_1
STUDENTS P_2016 P_2016_SP_2
STUDENTS P_2016 P_2016_SP_MAX
STUDENTS P_2017 P_2017_SP_1
STUDENTS P_2017 P_2017_SP_2
STUDENTS P_2017 P_2017_SP_MAX
STUDENTS P_MAX P_MAX_SP_1
STUDENTS P_MAX P_MAX_SP_2
STUDENTS P_MAX P_MAX_SP_MAX
9 rows selected.
3)Sub-partition stats are NOT collected by default.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TETSUSER',
TabName =>'STUDENTS');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
from user_tables
where table_name='STUDENTS';
TABLE_NAME NUM_ROWS LAST_ANAL
------------ ---------- ---------
STUDENTS 9 04-JAN-19
SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL
---------- -------------- ---------- ---------
STUDENTS P_2016 3 04-JAN-19
STUDENTS P_2017 3 04-JAN-19
STUDENTS P_MAX 3 04-JAN-19
SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,
NUM_ROWS,LAST_ANALYZED
from user_tab_subpartitions
where table_name='STUDENTS'
order by PARTITION_NAME,LAST_ANALYZED;
TABLE_NAME PARTITION_NA SUBPARTITION_NAME NUM_ROWS LAST_ANAL
------------- ------------ ----------------- ---------- ---------
STUDENTS P_2016 P_2016_SP_1
STUDENTS P_2016 P_2016_SP_2
STUDENTS P_2016 P_2016_SP_MAX
STUDENTS P_2017 P_2017_SP_1
STUDENTS P_2017 P_2017_SP_2
STUDENTS P_2017 P_2017_SP_MAX
STUDENTS P_MAX P_MAX_SP_1
STUDENTS P_MAX P_MAX_SP_2
STUDENTS P_MAX P_MAX_SP_MAX
9 rows selected.
4) With PartName option, all Global and Partition stats are collected, but not for Sub-partitions.
SQL> exec dbms_stats.delete_table_stats(ownname => 'TETSUSER',
tabname => 'STUDENTS');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TETSUSER',
TabName =>'STUDENTS',PartName=>'P_2016');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
from user_tables where table_name='STUDENTS';
TABLE_NAME NUM_ROWS LAST_ANAL
------------- ---------- ---------
STUDENTS 9 07-JAN-19
SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;
TABLE_NAME PARTITION_NA NUM_ROWS LAST_ANAL
------------- ------------ ---------- ---------
STUDENTS P_2016 3 07-JAN-19
STUDENTS P_2017 3 07-JAN-19
STUDENTS P_MAX 3 07-JAN-19
SQL> select TABLE_NAME,PARTITION_NAME,subpartition_name,
NUM_ROWS, LAST_ANALYZED
from user_tab_subpartitions
where table_name='STUDENTS'
order by PARTITION_NAME,LAST_ANALYZED;
TABLE_NAME PARTITION_NA SUBPARTITION_NAME NUM_ROWS LAST_ANAL
------------- ------------ ------------------ -------- ---------
STUDENTS P_2016 P_2016_SP_1
STUDENTS P_2016 P_2016_SP_2
STUDENTS P_2016 P_2016_SP_MAX
STUDENTS P_2017 P_2017_SP_1
STUDENTS P_2017 P_2017_SP_2
STUDENTS P_2017 P_2017_SP_MAX
STUDENTS P_MAX P_MAX_SP_1
STUDENTS P_MAX P_MAX_SP_2
STUDENTS P_MAX P_MAX_SP_MAX
9 rows selected.
5) With Granularity=>’ALL’, all Global, Partition and Sun-partition stats are collected.
SQL> exec dbms_stats.delete_table_stats(ownname => 'TESTUSER',
tabname => 'STUDENTS');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'TESTUSER',
TabName =>'STUDENTS', Granularity=>'ALL');
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,NUM_ROWS,LAST_ANALYZED
from user_tables where table_name='STUDENTS';
TABLE_NAME NUM_ROWS LAST_ANAL
------------- ---------- ---------
STUDENTS 9 07-JAN-19
SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,LAST_ANALYZED
from user_tab_partitions where table_name='STUDENTS' ;
TABLE_NAME PARTITION_NA NUM_ROWS LAST_ANAL
------------- ------------ ---------- ---------
STUDENTS P_2016 3 07-JAN-19
STUDENTS P_2017 3 07-JAN-19
STUDENTS P_MAX 3 07-JAN-19
SQL>select TABLE_NAME,PARTITION_NAME,subpartition_name,
NUM_ROWS,LAST_ANALYZED
from user_tab_subpartitions
where table_name='STUDENTS'
order by PARTITION_NAME,LAST_ANALYZED;
TABLE_NAME PARTITION_NA SUBPARTITION_NAME NUM_ROWS LAST_ANAL
------------- ------------ ----------------- --------- ---------
STUDENTS P_2016 P_2016_SP_1 1 07-JAN-19
STUDENTS P_2016 P_2016_SP_2 1 07-JAN-19
STUDENTS P_2016 P_2016_SP_MAX 1 07-JAN-19
STUDENTS P_2017 P_2017_SP_1 1 07-JAN-19
STUDENTS P_2017 P_2017_SP_2 1 07-JAN-19
STUDENTS P_2017 P_2017_SP_MAX 1 07-JAN-19
STUDENTS P_MAX P_MAX_SP_1 1 07-JAN-19
STUDENTS P_MAX P_MAX_SP_2 1 07-JAN-19
STUDENTS P_MAX P_MAX_SP_MAX 1 07-JAN-19
9 rows selected.
