1.0

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)
Default Partitioning
When a partitioned table has no PARTITION BY clause and the default table type is set to partitioned (the
table-default-partitioned property is set), SQLFire partitions the table based on these steps:
1. Check for default colocation by partitioning on the first foreign key for that can be colocated with its referenced
table (if the referenced table is partitioned on the same field). If the referenced table is not partitioned on the
primary key columns, then SQLFire tries the next available foreign key.
2. Partition by the primary key if one exists.
3. Partition by first unique key.
4. Partition by an internally-generated row id.
Example of Default Partitioning
-- COUNTRIES is by default hash partitioned on its primary
-- key, COUNTRY_ISO_CODE.
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) )
)
-- CITIES is automatically colocated with COUNTRIES and hash
-- partitioned on COUNTRY_ISO_CODE because CITIES has a
foreign key
-- referencing COUNTRIES and partitioning/colocation are
otherwise not
-- specified.
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)
)
459
SQL Language Reference