I was working on a package that we use to gather statistics on the partition level after data is loaded and found some quirks that apply when the partition has subpartitions.
- You can’t use dbms_stats.gather_table_stats to gather stats on just one subpartition. (SEE THE COMMENTS BELOW FOR CLARIFICATION ON THIS)
- If you gather stats on a partition with granularity=>’PARTITION’ it doesn’t gather stats at the subpartition level.
So, if you update some data in subpartitions of a partition and want to update statistics on both the partition and its subpartitions you have to call dbms_stats.gather_table_stats twice. Once with granularity=>’PARTITION’ and once with granularity=>’SUBPARTITION’.
i.e. If you have a subpartitioned table called SUBPARTAB and partition PART_20120101 and you want to gather stats at the partition and subpartition level you run the following commands.
execute dbms_stats.gather_table_stats( NULL,'SUBPARTAB','PART_20120101', granularity=>'PARTITION'); execute dbms_stats.gather_table_stats( NULL,'SUBPARTAB','PART_20120101', granularity=>'SUBPARTITION');
Note that there is no subpartition parameter to dbms_stats.gather_table_stats. You can only specify the partition name.
Here an example script that demonstrates gathering stats at the global, partition, and subpartition levels and shows which update subpartition stats.