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.

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

  5. In version 12.2 a single call to gather_table_stats with granularity=>’AUTO’ and the name of the partition – will generate statistics for the subpartitions of that partition and as well for the partition itself.

    • Bobby says:

      Thank you for your comment. I ran some tests and granularity AUTO with a partition name gathers stats at the partition, subpartition, and table level. This worked as far back as

      I was originally trying to gather stats at the partition and subpartition levels only with granularity partition. I tried the newer versions of Oracle up to 18c and they still do not gather subpartition stats with granularity PARTITION.

      Interesting stuff. Thanks for your input.


      • ctincristi says:

        Hi, Bobby,

        The fact that the table level stats are updated with the effect of the analyzed partition is different than the statistics gathering at table level with AUTO granularity, which actually runs the stats gathering for all partitions. Think of it like the number of rows coming from the partition is made known to the table level stats. So, for running stats at partition level including all its subpartitions, I think the one line that I wrote is good. Similarly, if you run just the subpartition stats, you will see that the partition and table level stats are also updated, but that does not mean that effort has been spent in analyzing other subpartitions or partitions. This is how I understand what is happening.

        • Bobby says:

          You could be right. I think it is just a matter of trying the different options and seeing which ones do what you want.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.