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:
Table 1. Supported data typesData 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.