11 July 2007

Gathering stats for ONLY a partition

I'm sure you all know to use DBMS_STATS to gather statistics for your tables and indexes. The DBMS_STATS.GATHER_TABLE_STATS() method has as its first three parameters OWNNAME, TABNAME, and PARTNAME, specifying the schema owner name, table name, and table partition name, respectively.

I had been assuming that specifying a partition name via the PARTNAME parameter would mean that I'd be gathering stats for just that partition. Wrong! You also need to specify GRANULARITY => PARTITION if that is your goal. Otherwise the PARTNAME is simply ignored and global (table-wide) statistics will be gathered instead.

Thanks to Wolfgang Breitling for the cluestick.

1 comment:

  1. Wolfgang BreitlingJuly 18, 2007 at 12:36 PM

    Just for clarification. Provided you haven't changed the granularity option from the default "DEFAULT", you still get the partition statistics updated, just not only the one partition you specified but all partitions AND the global statistics.