General Guidelines for Column Partitioning
- You cannot specify a column more than one time within a column partition.
- You cannot specify a column to be in more than one column partition.
- A column partition value consists of the values of the columns in the column partition for a specific table row.
- When the COLUMN or ROW format is system-determined, either because you did not explicitly specify COLUMN or ROW format or because you explicitly specified SYSTEM format, the database bases its choice on the size of a column partition value for the column partition and other factors such as whether a column partition value for the column partition has fixed or variable length and whether the column partition is a single-column or multicolumn partition.
As a general rule, a narrow column partition is determined to have COLUMN format and a wide column partition is determined to have ROW format.
The database considers a column partition to be narrow when its size is about 256 bytes or less. Anything else is judged to be wide.
The database estimates the size of a variable-length column in a column partition as its (maximum length / 3) + 2. This value depends on other factors that might make it smaller or larger, and is also subject to change if a more appropriate value is determined.
This size is chosen based on the number of noncompressed column partition values that could fit into a container, and this depends on how large containers are allowed to grow.
To be narrow, a large enough number of column partition values can be packed into a container to get row header compression benefits that offset any negative impacts from packing values into a container and retrieving values from a container.
You can use HELP COLUMN requests or retrieve the information using an appropriate data dictionary view to determine the format that the database selects for a column partition.
- A column partition has COLUMN format, ROW format, or SYSTEM format, but never a mix of formats. Different column partitions of a column-partitioned table or join index can have different formats. The column partitions of a column-partitioned table can have all COLUMN format, all ROW format, all SYSTEM format, or a mixture of formats.
Using COLUMN Grouping in the PARTITION BY Clause
The following rules apply to specifying column grouping for a COLUMN specification in the PARTITION BY clause.
| If... | ...Then | Otherwise, |
|---|---|---|
| you specify COLUMN in the PARTITION BY clause for a table or join index, | the table or join index is column-partitioned by definition. | |
| you specify MODIFY NO PRIMARY and you specify a PARTITION BY clause, | you must specify a COLUMN partitioning level. | The database returns an error to the requestor. |
| you specify non-group and group column partitions in the column group list, | the database defines a column partition for each non-group and group column partition. | |
| you do not specify a column grouping for a COLUMN clause, | the database defines a column partition for each column and column group specified in the column list for a CREATE TABLE request, or in the select list for a CREATE JOIN INDEX request. | |
| you specify ALL BUT with the column grouping specification, | the database defines a single-column partition with autocompression and a system-determined format of either COLUMN or ROW, depending on the column characteristics, for each column not specified in the column group list. | |
| you do not specify ALL BUT with the column grouping specification, | the database groups any columns not specified in the column group list into one column partition with autocompression and a system-determined format of either COLUMN or ROW, depending on the column characteristics. | |
| you specify COLUMN for a column partition, | the database stores one or more column partition values in a physical row called a container using COLUMN format. | |
| you specify ROW for a column partition, | the database stores only one column partition value in a physical row as a subrow. Subrow, or ROW, format is the standard way rows are stored. | |
| you specify SYSTEM for a column, | the database determines the column partition format based on the size of a column partition value for the remaining set of columns in the column partition and other factors such as whether a column partition value for the column partition has fixed or variable length. The database generally determines a narrow column partition to have COLUMN format and a wide column partition to have ROW format.
|
|
| you want the database to determine the format for a column partition, | specify SYSTEM. | you can explicitly specify COLUMN or ROW format if you want a specific format for a column partition. |
| you explicitly specify AUTO COMPRESS or NO AUTO COMPRESS for a column partition, | the database either does or does not apply autocompression for physical rows according to the specification. The database does apply any user-specified compression and, for column partitions with COLUMN format, row header compression. |
|
| you want to determine the format that the database selects for a column partition, | use HELP COLUMN requests or retrieve the information using an appropriate data dictionary view. | |
| you specify neither COLUMN nor ROW for a column partition or if you specify SYSTEM, | the database determines whether to use COLUMN or ROW format depending on the column characteristics. |