Vantage provides the user with the ability to collect with “PARTITION statistics” based on partition numbers rather than column values. This enables the Optimizer to more accurately estimate cost operations involving a partitioned table.
The Optimizer is provided with:
- The number of partitions that are nonempty
- How the rows are distributed among the nonempty partitions.
Partition statistics can be collected for just the PARTITION column (single-column partition statistics) or on the PARTITION column and other table columns (multicolumn PARTITION statistics). Collecting statistics on the partitioning columns is also recommended. When the Optimizer has this information, it can better calculate the relative cost of various methods of optimizing a query for a partitioned table.
Having PARTITION statistics allows the Optimizer to generate a better plan for partitioned tables. For example, the Optimizer can cost joins involving partitioned tables more accurately with PARTITION statistics.