1.1.1

Description
If no partitioning clause is specified, then the table is replicated on all the members of the server groups of the
table. REPLICATE can also be explicitly used to create a replicated table.
See also:
• Partitioning by ColumnExample with PARTITION BY COLUMN Clause on page 499
• Partitioning by a Range of ValuesExample with PARTITION BY RANGE Clause on page 499
• Partitioning by a List of ValuesExample of List Partitioning on page 500
• Partitioning by ExpressionExample of PARTITION BY ( expression ) on page 500
• Colocating Related RowsExample of Colocating Related Rows from Multiple Tables on page 500
• REDUNDANCY ClauseExample with REDUNDANCY Clause on page 501
• BUCKETS Clause on page 502
• RECOVERYDELAY Clause on page 502
• MAXPARTSIZE ClauseExample with BUCKETS, RECOVERYDELAY, and MAXPARTSIZE on page 502
Partitioning by Column
Use the PARTITION BY COLUMN clause to provide a set of column names that will determine the partitioning.
As a shortcut you can use PARTITION BY PRIMARY KEY to refer to the primary key columns. The hash
function is an internal function that typically returns the EXCLUSIVE OR of the hashcode() return values for
the columns' underlying Java types.
Example with PARTITION BY COLUMN Clause
-- partition by primary key
CREATE TABLE HOTELAVAILABILITY
( HOTEL_ID INT NOT NULL,
BOOKING_DATE DATE NOT NULL,
ROOMS_TAKEN INT DEFAULT 0,
PRIMARY KEY (HOTEL_ID, BOOKING_DATE)
) PARTITION BY PRIMARY KEY
-- partition by column (booking date only)
CREATE TABLE HOTELAVAILABILITY
( HOTEL_ID INT NOT NULL,
BOOKING_DATE DATE NOT NULL,
ROOMS_TAKEN INT DEFAULT 0,
PRIMARY KEY (HOTEL_ID, BOOKING_DATE)
) PARTITION BY COLUMN (BOOKING_DATE)
Partitioning by a Range of Values
Use the PARTITION BY RANGE clause to specify the ranges of a field that should be colocated. Doing so
ensures the locality of data in case of range queries and for cross table joins. The lower limit of the range is
inclusive and the upper limit is exclusive. It is not necessary for the ranges to cover the whole spectrum of the
possible values for the field. The ranges not covered are automatically partitioned on the available servers by
SQLFire with no guarantee of locality for those values.
Example with PARTITION BY RANGE Clause
-- partition by range
CREATE TABLE HOTELAVAILABILITY
( HOTEL_ID INT NOT NULL,
BOOKING_DATE DATE NOT NULL,
ROOMS_TAKEN INT DEFAULT 0,
PRIMARY KEY (HOTEL_ID, BOOKING_DATE)
499
SQL Language Reference