Range Partitions

Range partitioning divides large tables by a range of partition-key values established for each partition.

As part of an information life cycle management strategy, range partitioning can shorten backup and restore times; provide a finer level of granularity for data validation; and support tiered storage.

In a range-partitioning-scheme, the partition-key is the column whose value determines the partition that the row belongs to:

range-partitioning-scheme: 
    PARTITION BY RANGE( partition-key ) ( range-partition-decl [ , range-partition-decl ... ] )

The range-partition-declaration determines how a named partition is placed in a dbspace:

range-partition-declaration:
    range-partition-name VALUES <= ( {constant |  MAX } ) [ IN dbspace-name ]

The VALUES clause identifies the upper bound for each partition (in ascending order). Each range partition can be placed in its own dbspace and managed individually. Partition names must be unique within the set of partitions on a table.

Restrictions

Range partitioning is restricted to a single partition key column and a maximum of 1024 partitions.

Example

The most common form of range partitioning is to partition a table by date. This example creates a range partitioned table bar with six columns and three partitions, mapping data to partitions based on dates:

CREATE TABLE bar (
    c1 INT IQ UNIQUE(65500),
    c2 VARCHAR(20),
    c3 CLOB PARTITION (P1 IN Dsp11, P2 IN Dsp12,
        P3 IN Dsp13),
`   c4 DATE,
    c5 BIGINT,
    c6 VARCHAR(500) PARTITION (P1 IN Dsp21,
        P2 IN Dsp22),
    PRIMARY KEY (c5) IN Dsp2) IN Dsp1
    PARTITION BY RANGE (c4)
    (P1 VALUES <= ('2006/03/31') IN Dsp31, 
	 P2 VALUES <= ('2006/06/30') IN Dsp32,
	 P3 VALUES <= ('2006/09/30') IN Dsp33);