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.
- Bobby
You can indeed gather stats on only a subpartition: Just name the subpartition in the partname parameter.
On a given table, partitions and subpartitions share a common namespace–that is, you can’t have a partition and a subpartition with the same name. So there’s no risk of ambiguity: If you specify a name, it has to refer to either a partition or a subpartition; it can’t refer to both.
I tried it just now on 10.2.0.4 and it worked. (I did give granularity => ‘SUBPARTITION’ which might have helped.)
You do make a good point in that Oracle failed to supply us with a granularity level of ‘PARTITION AND SUBPARTITION’, so if you want to analyze a partition and all its subpartitions, you need to make two calls to DBMS_STATS.
Jason,
I ran a quick test and you are right. This is helpful information for me. If you put the subpartition name into the partition parameter to dbms_stats.gather_table_stats it will gather statistics on just one subpartition. Thanks for your reply!
Here is my testcase proving this:
http://www.bobbydurrettdba.com/uploads/subpartitionstats2.zip
- Bobby