1.1.1

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)
) PARTITION BY COLUMN (COUNTRY_ISO_CODE)
COLOCATE WITH (COUNTRIES)
REDUNDANCY Clause
Use the REDUNDANCY clause to specify the number of redundant copies that should be maintained for each
partition, to ensure that the partitioned table is highly available even if members fail. Because SQLFire is primarily
a memory-based data management system, it is important to configure this carefully to enable failover in case
of failures. A value too large adversely affects performance, network usage, and memory usage. Typically a
value of 1 is recommended; this setting maintains one copy in addition to the primary copy of the table data.
Note: Colocated partitioned tables must have the same redundancy.
501
SQL Language Reference