Table partitioning keys
A table partitioning key is an ordered set of one or more columns in a table. The values in the table partitioning key columns are used to determine in which data partition each table row belongs.
To define the table partitioning key on a table use the CREATE TABLE statement with the PARTITION BY clause.
- Define range granularity to match data roll-out. It is most common to use week, month, or quarter.
- Define ranges to match the data roll-in size. It is most common to partition data on a date or time column.
- Partition on a column that provides advantages in partition elimination.
Supported data types
Table 1 shows the data types (including synonyms) that are supported for use as a table partitioning key column:
|Data type column 1
|Data type column 2
|CHARACTER FOR BIT DATA
|CHAR FOR BIT DATA
|VARCHAR FOR BIT DATA
|CHARACTER VARYING FOR BIT DATA
|CHAR VARYING FOR BIT DATA
|User defined types (distinct)
Unsupported data types
- User defined types (structured)
- LONG VARCHAR
- LONG VARCHAR FOR BIT DATA
- BINARY LARGE OBJECT
- CHARACTER LARGE OBJECT
- LONG VARGRAPHIC
- Varying length string for C
- Varying length string for Pascal
in eight data partitions, one for each quarter in year 2001 and 2002.
CREATE TABLE sales (year INT, month INT)
PARTITION BY RANGE(year, month)
(STARTING FROM (2001, 1) ENDING (2001,3) IN tbsp1,
ENDING (2001,6) IN tbsp2, ENDING (2001,9)
IN tbsp3, ENDING (2001,12) IN tbsp4,
ENDING (2002,3) IN tbsp5, ENDING (2002,6)
IN tbsp6, ENDING (2002,9) IN tbsp7,
ENDING (2002,12) IN tbsp8)
- When multiple columns are used as the table partitioning key, they are treated as a composite key (which are similar to composite keys in an index), in the sense that trailing columns are dependent on the leading columns. Each starting or ending value (all of the columns, together) must be specified in 512 characters or less. This limit corresponds to the size of the LOWVALUE and HIGHVALUE columns of the SYSCAT.DATAPARTITIONS catalog view. A starting or ending value specified with more than 512 characters will result in error SQL0636N, reason code 9.
- Table partitioning is multicolumn not multidimension. In table partitioning, all columns used are part of a single dimension.
Generated columns can be used as table partitioning keys. This example creates a table with twelve data partitions, one for each month. All rows for January of any year will be placed in the first data partition, rows for February in the second, and so on.
CREATE TABLE monthly_sales (sales_date date,
sales_month int GENERATED ALWAYS AS (month(sales_date)))
PARTITION BY RANGE (sales_month)
(STARTING FROM 1 ENDING AT 12 EVERY 1);
- You cannot alter or drop the expression of a generated column that is used in the table partitioning key. Adding a generated column expression on a column that is used in the table partitioning key is not permitted. Attempting to add, drop or alter a generated column expression for a column used in the table partitioning key results in error (SQL0270N rc=52).
- Data partition elimination will not be used for range predicates if the generated column is not monotonic, or the optimizer can not detect that it is monotonic. In the presence of non-monotonic expressions, data partition elimination can only take place for equality or IN predicates. For a detailed discussion and examples of monotonicity see Considerations when creating MDC or ITC tables.