1.1

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.
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 SQLFire 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.
When you colocate tables that are partitioned by expression, SQLFire only ensures that the parent table is also
partitioned by an expression. No additional compatibility checks are performed. You must ensure that the
expressions resolve to the same values in both tables, so that data can be colocated.
The COLOCATE WITH clause is only used as part of a partitioning clause. 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 the clause used to create 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.
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,
CITY_NAME VARCHAR(24) NOT NULL,
COUNTRY VARCHAR(26) NOT NULL,
AIRPORT VARCHAR(3),
LANGUAGE VARCHAR(16),
COUNTRY_ISO_CODE CHAR(2) CONSTRAINT COUNTRIES_FK
REFERENCES COUNTRIES (COUNTRY_ISO_CODE)
vFabric SQLFire User's Guide486
vFabric SQLFire Reference