Subpartition stats quirks

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.

  1. You can’t use dbms_stats.gather_table_stats to gather stats on just one subpartition. (SEE THE COMMENTS BELOW FOR CLARIFICATION ON THIS)
  2. 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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Subpartition stats quirks

  1. Jason Bucata says:

    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.

Leave a Reply