Hash Partitions

Hash partitioning maps data to partitions based on partition-key values processed by an internal hashing function.

Hash partitioning distributes data to logical partitions for parallel execution, which can enhance join performance on large tables and distributed queries (DQP).

In a hash-partitioning-scheme declaration, a partition-key is a column or group of columns, whose composite value determines the partition where each row of data is stored:

hash-partitioning-scheme:
    PARTITION BY HASH  ( partition-key [ , partition-key, … ] )

Hash partition keys are restricted to a maximum of eight columns with a combined declared column width of 5300 bytes or less. For hash partitions, the table creator determines only the partition key columns; the number and location of the partitions are determined internally.

Restrictions

You cannot add, drop, merge, or split a hash partition.
You cannot add or drop a column from a hash partition key.

Examples

In this example, table tbl42 includes a PRIMARY KEY (column c1) and a HASH PARTITION KEY (columns c4 and c3).

CREATE TABLE tbl42 (
  c1 BIGINT NOT NULL,
  c2 CHAR(2) IQ UNIQUE(50),
  c3 DATE IQ UNIQUE(36524),
  c4 VARCHAR(200),
  PRIMARY KEY (c1) 
  PARTITION BY HASH ( c4, c3 )
  )

This example shows the common case where the join key is both the primary key and the hash partitioning key.

CREATE TABLE bar (
  c1 BIGINT NOT NULL,
  c2 CHAR(2) IQ UNIQUE(50),
  c3 DATE IQ UNIQUE(36524),
  c4 VARCHAR(200),
  c5 CLOB,
  PRIMARY KEY ( c1 ) 
  PARTITION BY HASH ( c1 )
)