The specification order of partitioning expressions can be important for multilevel partitioning. The system maps multilevel partitioning expressions into a single-level combined partitioning expression, and then maps the resulting combined partition number 1-to-1 to an internal partition number. Rows are in logical RowID order, where a RowID consists of an internal partition number, a row hash value, and a row uniqueness value (RowIDs are identical for single-level and multilevel partitioning).
This implies a partial physical ordering based on how the file system manages the data blocks and cylinders, though this is not a strict relationship. This physical ordering maintains the ordering of partitions (except for a possible wraparound of internal numbers at one point in the internal number sequence for each level). Multilevel partitioning expressions are analogous with a single-level partitioning expression that is identical to the combined partitioning expression for multilevel partitioning, at least in terms of expressing how rows are logically ordered by the file system.
- Query performance
Row partition elimination at the lowest levels can increase overhead because of the frequent need to skip to the next internal partition to be read. This is because a partition at a lower level is split among the partitions at higher levels in the partition hierarchy.
At higher levels in the partition hierarchy, there are more contiguous internal partitions to scan and skip.
- Bulk data load performance
If a load is order-based on one of the partitioning columns, having that partitioning column at the highest level in the partition hierarchy can improve load performance because those partitions are contiguous. For example, a date-based partition with daily data loads may benefit from having the date-based partitioning at the first level.
You define the order of the partitioning expressions, and that ordering implies the logically ordering by RowID. Because the partitions at each level are distributed among the partitions of the next higher level in the hierarchy, scanning a partition at a certain level requires skipping internal partitions.
If the number of rows in each internal partition is large, then skipping to the next internal partition to be read incurs relatively little overhead.
If the system reads or skips a small number of rows for each internal partition, performance may be worse than a full-table scan.
Partition expression order does not affect the ability to eliminate partitions, but does affect the efficiency of a partition scan. This is not a concern if there are a large number of rows or multiple datablocks in each of the nonempty internal partitions.
- For a table with 65,535 combined partitions, the maximum number of combined partitions per partitioned primary index is 6.5535 x 109 (billion) rows per AMP, 100 byte rows, and 50 KB data blocks, and assuming an equal distribution of rows among the partitions, each combined partition spans 200 to 201 data blocks.
Skipping internal partitions does not incur significant overhead. All or most rows in the data blocks read qualify, and the system skips at least 199 data blocks between each set of data blocks read.
- If the table has only 6.5535 x 106 (million) rows per AMP, each combined partition has about 0.2 data blocks. In the worst case, where the system eliminates only every other partition at the lowest level, every data block is read, and a full-table scan is more efficient.
If there are 5 partitions at the lowest level, and the system can eliminate 4 out of 5 partitions, but still must read every data block.
If there are 6 partitions at the lowest level, and the system can eliminate 5 out of the 6, the system can skip data blocks, but possibly not enough to overcome the overhead burden, so this may not be more efficient than a full-table scan.
With a large number of partitions at the lowest level, and a large number of eliminated partitions at the lowest level, the system can typically skip enough more data blocks to overcome the overhead burden, making the operation may be more efficient than a full-table scan.
This second case is not a good use of multilevel partitioning. Instead, consider an alternative partitioning that produces multiple data blocks for each nonempty internal partition.
A partitioning scheme that defines fewer levels of partitioning and fewer partitions per level, where the lowest level has the greatest number of partitions, makes sure that there are more rows per combined partition, and is far more useful. For example, if one level was initially partitioned in intervals of one day, changing the interval to one week or one month may be better.
Row partition elimination at the lower partition levels of a row partition hierarchy requires more skipping, which can both cause more I/O operations and increase the CPU path length.
To achieve optimal performance, specify a partitioning expression that is more likely to evoke row partition elimination for queries at a higher level and specify those expressions that are not as likely to evoke row partition elimination either at a lower level, or not at all. Also consider specifying the row partitioning expression with the greatest number of row partitions at the lowest level.
The order of the row partitioning expressions may not be a significant concern if each nonempty combined partition has a large number of data blocks.
You can use the following query to find the average number of rows per populated combined row partition.
SELECT AVG(pc) FROM (SELECT COUNT(*) AS pc FROM t GROUP BY PARTITION) AS pt;
Assuming the average block size is b and the row size is r, you can use the following query to find the average number of data blocks per nonempty combined row partition. The ideal number of data blocks per nonempty combined row partition is 10 or more.
USING (b FLOAT, r FLOAT) SELECT (:r / :b) * AVG(pc) FROM (SELECT COUNT(*) AS pc FROM t GROUP BY PARTITION) AS pt;