1.0

PRIMARY KEY (HOTEL_ID, BOOKING_DATE)
) PARTITION BY COLUMN (BOOKING_DATE)
PARTITION BY RANGE Clause
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)
) PARTITION BY RANGE (BOOKING_DATE)
( VALUES BETWEEN '2010-01-01' AND '2010-04-01',
VALUES BETWEEN '2010-04-01' AND '2010-07-01',
VALUES BETWEEN '2010-07-01' AND '2010-10-01',
VALUES BETWEEN '2010-10-01' AND '2011-01-01'
)
PARTITION BY LIST Clause
Use the PARTITION BY LIST clause to specify the set of values of a field that should be colocated to help
optimization of queries and for cross table joins. It is not necessary to list all the possible values for the field,
and the values that are not covered are partitioned automatically.
Example of List Partitioning
-- partition by list
CREATE TABLE FLIGHTS
(
FLIGHT_ID CHAR(6) NOT NULL ,
SEGMENT_NUMBER INTEGER NOT NULL ,
ORIG_AIRPORT CHAR(3),
PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
) PARTITION BY LIST (ORIG_AIRPORT)
( VALUES ('PDX', 'LAX'),
VALUES ('AMS', 'DUB'),
VALUES ('DTW', 'ORL')
)
PARTITION BY Expression Clauses
The PARTITION BY clause with an expression is a type of hash-like partitioning where the value to hash on is
evaluated from the expression. The expression must reference only field names from the table. This allows rows
to be colocated based on some function of their values.
457
SQL Language Reference