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.
Choosing an effective table partitioning key column is essential
to taking full advantage of the benefits of table partitioning. The
following guidelines can help you to choose the most effective table
partitioning key columns for your partitioned table.
- 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 |
---|---|
SMALLINT | INTEGER |
INT | BIGINT |
FLOAT | REAL |
DOUBLE | DECIMAL |
DEC | DECFLOAT |
NUMERIC | NUM |
CHARACTER | CHAR |
VARCHAR | DATE |
TIME | GRAPHIC |
VARGRAPHIC | CHARACTER VARYING |
TIMESTAMP | CHAR VARYING |
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
The following data
types can occur in a partitioned table, but are not supported for
use as a table partitioning key column:
- User defined types (structured)
- LONG VARCHAR
- LONG VARCHAR FOR BIT DATA
- BLOB
- BINARY LARGE OBJECT
- CLOB
- CHARACTER LARGE OBJECT
- DBCLOB
- LONG VARGRAPHIC
- REF
- Varying length string for C
- Varying length string for Pascal
- XML
If you choose to automatically generate data partitions
using the EVERY clause of the CREATE TABLE statement, only one column
can be used as the table partitioning key. If you choose to manually
generate data partitions by specifying each range in the PARTITION
BY clause of the CREATE TABLE statement, multiple columns can be
used as the table partitioning key, as shown in the following example:
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)
This results
in eight data partitions, one for each quarter in year 2001 and 2002.Note:
- 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
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.
Example 1
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);
Note:
- 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.