1.1.1

) 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'
)
Partitioning by a List of Values
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')
)
Partitioning by Expression
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.
Note: When you colocate tables that are partitioned by expression, you must ensure that the expressions
resolve to the same values in both tables, so that data can be colocated. SQLFire ensures that the parent
table uses an expression for partitioning, but it performs no other compatibility checks.
Example of PARTITION BY ( expression )
-- partition by expression
-- This example will colocate all the rows that are booked
-- in the same month together
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 (MONTH(BOOKING_DATE))
Colocating Related Rows
The COLOCATE WITH clause specifies the table with which a new partitioned table must be colocated. The
referenced table must already exist.
vFabric SQLFire User's Guide500
vFabric SQLFire Reference