Creation of partitioned tables

 

New partitioned tables can be created using the CREATE TABLE statement.

The table definition must include the table name and the names and attributes of the columns in the table. The definition might also include other attributes of the table, such as the primary key.

There are two methods available for partitioning: hash partitioning and range partitioning. Hash partitioning places rows at random intervals across a user-specified number of partitions and key columns. Range partitioning divides the table based on user-specified ranges of column values. Specify the type of partitioning you want to use with the PARTITION BY clause. For example, to partition table PAYROLL in library PRODLIB with partitioning key EMPNUM into four partitions, use the following code: 

CREATE TABLE PRODLIB.PAYROLL 	(EMPNUM INT,
	 FIRSTNAME CHAR(15),
    LASTNAME CHAR(15),
    SALARY INT)
PARTITION BY HASH(EMPNUM)
INTO 4 PARTITIONS

Or, to partition PAYROLL by range, use the following code: 

CREATE TABLE PRODLIB.PAYROLL 	(EMPNUM INT,
	 FIRSTNAME CHAR(15),
    LASTNAME CHAR(15),
    SALARY INT)
PARTITION BY RANGE(EMPNUM)
(STARTING FROM (MINVALUE) ENDING AT (500) INCLUSIVE, 
STARTING FROM (501) ENDING AT (1000) INCLUSIVE, 
STARTING FROM (1001) ENDING AT (MAXVALUE)

This statement results in a table that contains three partitions. The first partition contains all rows where EMPNUM is less than or equal to 500. The second partition contains all rows where EMPNUM is between 501 and 1000 inclusive. The third partition contains all rows where EMPNUM is greater than or equal to 1001. The following figure shows a table with data partitioned according to these values. Figure 1. Employee information partitioned

This image shows a table of employee information partitioned by range

When a partitioned table is created, a system-supplied check constraint is added to each partition. This check constraint cannot be displayed, altered, or removed by the user.

For range partitioning, this check constraint validates that the data is in the proper range. Or, if the partition allows null values, the check constraint validates that the data is null.

For hash partitioning, this check constraint validates that the data based on the condition Partition number = MOD(Hash(fields), Number of partitions) + 1 where the Hash function returns a value between 0 and 1023. The null values are always placed in the first partition.

See the CREATE TABLE statement in the SQL reference topic collection for partitioning clauses and syntax diagrams.

 

Parent topic:

Partitioned tables

 

Related concepts


From a nonpartitioned table to a partitioned table

 

Related tasks


CREATE TABLE

 

Related reference


SQL reference
Check constraint optimization