# 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

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.

### 5 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.

• Bobby says:

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:

– Bobby

2. Geethosh says:

If we are not gathering subparition level stats, will it have any impact on performance? Is there any specific reason we should or should not gather stats on subpartition?

• Bobby says:

Geethosh,

Thank you for you comment. Unfortunately I don’t know the complete answer to your question. If you have any queries that only touch one subpartition then you definitely need stats on that subpartition. But, I’m not sure whether you need them if you have queries that span multiple subpartitions. I used to think that such queries would not use subpartition stats but they may in certain cases.

It is a good question and maybe others can give you a more complete answer.

– Bobby

3. Anonymous says: