- While you can define column partitioning at any level of multilevel partitioning, in most cases, follow these guidelines when you configure the partitioning for a column-partitioned table.
- Code the column partitioning level first and follow the column partitioning level with any row partitioning levels.
- Code the column partition at the first level or as the second level after DATE or TIMESTAMP row partitioning.
The following considerations may lead to putting the column partitioning at a lower level.
- Potential improvements for cylinder migration.
- Block compression effectiveness.
- If you specify row partitioning as part of a multilevel column partitioning for a table, consider specifying the ADD option for the any partitioning levels that may need to increase their number of partitions.
- Unless you have a good reason not to, use the defaults when you specify PARTITION BY COLUMN. Do not override the defaults without first giving the reasons for doing so serious consideration
- For columns that are often specified in queries, but where the specific set of columns specified varies from request to request, create single-column partitions for the frequently specified columns.
- For wide column partitions, use ROW format, which has less overhead than a container that holds a small number of values.
If Vantage does not assign ROW format for a column partition, but you have determined that ROW format decreases space usage, specify ROW explicitly.
- Use COLUMN format for narrow column partitions, especially if autocompression is effective.
If Vantage does not assign COLUMN format for a multicolumn partition, but COLUMN is user-determined to be more appropriate (decreases space usage, and so on), specify COLUMN explicitly.
You may need to specify COLUMN format explicitly for a multicolumn partition that contains a column with a VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC, or VARBYTE data type and defined with a large maximum value, but where values are short in most cases. This is because the system-determined format may be ROW-based on the large maximum length.