1.1

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_11_bNNNNN/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'))
)
PARTITION BY COLUMN (FLIGHT_ID);
The remaining facts tables are also partitioned, and also colocated with the FLIGHTS table. For example:
CREATE TABLE FLIGHTAVAILABILITY
(
FLIGHT_ID CHAR(6) NOT NULL ,
vFabric SQLFire User's Guide40
Getting Started with vFabric SQLFire