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.
|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.|