1.0

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))
COLOCATE WITH Clause
The COLOCATE WITH clause specifies the table with which the new table must be colocated. The referenced
table must already exist.
When two tables are partitioned on columns and colocated, it forces partitions with the same values for those
columns in the two tables to be located on the same member. For example, in case of range or list partitioning
the rows that satisfy a particular range or list are colocated on the same member for all of the colocated tables.
If no PARTITION BY clause is used when there is a COLOCATE WITH clause, SQLFire throws a SQLException.
The partitioning clause that you specify for the new table must be compatible with clause used in the referenced
table.
For partitioned tables, any SERVER GROUPS clause that you specify must be the same as the clause used to
create the referenced table. For replicated tables to be colocated, the server groups must be identical or the server
groups of one table must be a subset of the server groups for the other table.
The number of BUCKETS for partitioned tables must also be the same, or SQLFire throws an exception.
If you do not specify a COLOCATE WITH clause, SQLFire attempts to perform default colocation by using
foreign key references. Default colocation is performed only if you omit the BUCKETS clause entirely, or if
you specify a BUCKETS value that matches the BUCKETS value of the referenced tables.
Example of Colocating Related Rows from Multiple Tables
CREATE TABLE COUNTRIES
(
COUNTRY VARCHAR(26) NOT NULL CONSTRAINT COUNTRIES_UNQ_NM
Unique,
COUNTRY_ISO_CODE CHAR(2) NOT NULL CONSTRAINT COUNTRIES_PK
PRIMARY KEY,
REGION VARCHAR(26),
CONSTRAINT COUNTRIES_UC
CHECK (country_ISO_code = upper(country_ISO_code) )
) PARTITION BY PRIMARY KEY
-- Explicitly colocate the CITIES table with the COUNTRIES
table.
-- Both of these tables will be hash partitioned on the
-- COUNTRY_ISO_CODE
-- field. This guarantees that cities are colocated with
-- the country they are in.
CREATE TABLE CITIES
(
CITY_ID INTEGER NOT NULL CONSTRAINT CITIES_PK Primary key,
vFabric SQLFire User's Guide458
vFabric SQLFire Reference