1.0

The AIRLINES, CITIES, COUNTRIES, and MAPS tables are treated as dimension tables, and are replicated
across the SQLFire cluster. In the tutorial it is assumed that applications frequently join these related tables based
on the FLIGHT_ID column, which is chosen as the partitioning column.
FLIGHTS, FLIGHTS_HISTORY, and FLIGHTAVAILABILITY are fact tables, and they will be partitioned.
You will co-locate these tables to ensure that all rows that are associated with FLIGHT_ID are maintained in a
single partition. This step ensures that frequent join queries based on a selected flight are pruned to a single
member and executed efficiently.
Procedure
1.
In a separate terminal window or GUI editor, open the create_colocated_schema.sql file in the
vFabric_SQLFire_10x/quickstart directory to examine the included DDL commands. The SQL
script begins by dropping the existing tables in the schema:
DROP TABLE AIRLINES;
DROP TABLE CITIES;
DROP TABLE COUNTRIES;
DROP TABLE FLIGHTAVAILABILITY;
DROP TABLE FLIGHTS;
DROP TABLE MAPS;
DROP TABLE FLIGHTS_HISTORY;
Dimension tables can be replicated using the same basic CREATE statement from the preceding section of
the tutorial. However, this script explicitly adds the REPLICATE keyword for clarity. For example:
CREATE TABLE AIRLINES
(
AIRLINE CHAR(2) NOT NULL CONSTRAINT AIRLINES_PK PRIMARY KEY,
AIRLINE_FULL VARCHAR(24),
BASIC_RATE DOUBLE PRECISION,
DISTANCE_DISCOUNT DOUBLE PRECISION,
BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
ECONOMY_SEATS INTEGER,
BUSINESS_SEATS INTEGER,
FIRSTCLASS_SEATS INTEGER
) REPLICATE;
The FLIGHTS table is partitioned based on the FLIGHT_ID column:
CREATE TABLE FLIGHTS
(
FLIGHT_ID CHAR(6) NOT NULL ,
SEGMENT_NUMBER INTEGER NOT NULL ,
ORIG_AIRPORT CHAR(3),
DEPART_TIME TIME,
DEST_AIRPORT CHAR(3),
ARRIVE_TIME TIME,
MEAL CHAR(1),
FLYING_TIME DOUBLE PRECISION,
MILES INTEGER,
AIRCRAFT VARCHAR(6),
CONSTRAINT FLIGHTS_PK PRIMARY KEY (
FLIGHT_ID,
SEGMENT_NUMBER),
CONSTRAINT MEAL_CONSTRAINT
CHECK (meal IN ('B', 'L', 'D', 'S'))
vFabric SQLFire User's Guide32
Getting Started with vFabric SQLFire