Hash-Range Partitions

Hash-range partitioning is a composite partitioning scheme that subpartitions a hash-partitioned table by range.

Hash-range partitioning provides the benefits of hash partitioning and range partitioning. Hash partitioning provides the best distributed query performance; range sub-partitioning enhances administrative tasks as part of an information life cycle management strategy.

In a hash-range-partitioning-scheme declaration, a SUBPARTITION BY RANGE clause adds a new range subpartition to an existing hash-range partitioned table:

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

The hash partition specifies how the data is logically distributed and colocated; the range subpartition specifies how the data is physically placed. The new range subpartition is logically partitioned by hash with the same hash partition keys as the existing hash-range partitioned table. The range subpartition key is restricted to one column.

Examples

This example (table tbl42) includes a PRIMARY KEY (column c1) and a hash partition key (columns c4 and c2) and a range subpartition key (column c3) :

CREATE TABLE tbl42 (
   c1 bigint not null,
   c2 char(2) iq unique(50),
   c3 date
   c4 varchar(200),
   PRIMARY KEY (c1)) IN Dsp1 
   PARTITION BY HASH (c4, c2)
   SUBPARTITION BY RANGE (c3)
   (P1 VALUES <= (‘2011/03/31’) IN Dsp31,
    P2 VALUES <= (‘2011/06/30’) IN Dsp32,
    P3 VALUES <= (‘2011/09/30’) IN Dsp33);

In this example, table tbl43 includes different dbspaces for storage of RANGE subpartitions for column c4. This syntax is similar to existing syntax for RANGE partitions. Note the use of SUBPARTITION in column c4.

CREATE TABLE tbl43 (
   c1 bigint not null,
   c2 char(2) iq unique(50),
   c3 date
   c4 varchar(200) 
   SUBPARTITION (P1 in Dsp331, P2 in Dsp332, P3 in Dsp333),
   PRIMARY KEY (c1)) IN Dsp1 
   PARTITION BY HASH (c4, c2)
   SUBPARTITION BY RANGE (c3)
   (P1 VALUES <= (‘2011/03/31’) IN Dsp31,
    P2 VALUES <= (‘2011/06/30’) IN Dsp32,
    P3 VALUES <= (‘2011/09/30’) IN Dsp33) ;