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(

execute dbms_stats.gather_table_stats(

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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

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

  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:


      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:

    thanks for this page, helpful. Tim

  4. waseem says:

    How about using execute dbms_stats.gather_table_stats(NULL,’SUBPARTAB’,granularity=>’ALL’);

    • Bobby says:

      Thank you for your comment.

      I did a quick test and if you gather stats at the table level with granularity=’ALL’ it does gather stats at the table, partition, and subpartition level. I think my original point was that you can’t gather stats on one partition and all of its subpartitions with one call to dbms_stats.gather_table_stats. Your example shows that you can do this at the table level.

Leave a Reply