The following query reveals that several of the recently added subpartitions in the COMPLETED_ORDERS partition have not yet been analyzed:
SELECT P.PARTITION_NAME "partition name",P.NUM_ROWS "partition rows",P.LAST_ANALYZED "last analyzed", SP.SUBPARTITION_NAME "subpart name", SP.NUM_ROWS "subpartition rows",SP.LAST_ANALYZED "subpart last analyzed" FROM DBA_TAB_PARTITIONS P INNER JOIN DBA_TAB_SUBPARTITIONS SP ON (P.TABLE_NAME = SP.TABLE_NAME) WHERE P.TABLE_NAME='ORDERS' AND P.PARTITION_NAME = SP.PARTITION_NAME AND P.PARTITION_NAME = 'COMPLETED_ORDERS' AND SP.NUM_ROWS IS NULL ORDER BY P.PARTITION_NAME DESC,SP.SUBPARTITION_NAME;
Output:
partition name | partition rows | last analyzed | subpart name | subpartition rows | subpart last analyzed |
---|---|---|---|---|---|
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2017 | ||
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2018 | ||
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2019 | ||
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2020 |
To analyze them, use dbms_stats with the PART_NAME directive, and the GRANULARITY directive set to "SUBPARTITION"
BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS ( OwnName => 'SCOTT' ,TabName => 'ORDERS' ,partname => 'COMPLETED_ORDERS' ,Estimate_Percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'FOR ALL COLUMNS SIZE AUTO' ,Degree => DBMS_STATS.AUTO_DEGREE ,Cascade => DBMS_STATS.AUTO_CASCADE ,granularity => 'SUBPARTITION' ,No_Invalidate => DBMS_STATS.AUTO_INVALIDATE); END; /After statistics gathering, execute the query again, but without the "AND SP.NUM_ROWS IS NULL" predicative:
partition name | partition rows | last analyzed | subpart name | subpartition rows | subpart last analyzed |
---|---|---|---|---|---|
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2017 | 0 |
27/12/2016 09:10:04 |
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2018 | 0 |
27/12/2016 09:10:05 |
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2019 | 0 |
27/12/2016 09:10:07 |
COMPLETED_ORDERS | 2527257 |
19/10/2015 07:17:44 | CO_SP_2020 | 0 |
27/12/2016 09:10:08 |
The 12.2 version of dbms_stats.gather_table_stats is documented here
No comments:
Post a Comment