Overview | Criteria | Table Joins | Aggregations | Ordering

Table joins are usually optimized by creating indexes on the table constraints. For relational databases, it is only necessary to index the foreign keys since the relational database's indexes on the primary keys will suffice. For raw data files, both the primary and foreign keys should be indexed.

Multi-column constraints should be indexed using multi-column Omnidex indexes. These are custom indexes that combine multiple columns into an index. The order of the columns in the indexes should match the order of the columns in the constraint.

Not all joins follow actual constraints. Sometimes there is a parent-child relationship between tables but no formal constraint is declared. In these cases, create Omnidex indexes as though a constraint was declared.

The following examples show how to evaluate queries and choose the Omnidex indexes. Below the examples is an Omnidex Environment File that will generate these indexes.

In this example, the HOUSEHOLD column is a primary constraint in HOUSEHOLDS and a foreign constraint in INDIVIDUALS.

  select     I.NAME, H.ADDRESS, H.CITY, H.STATE, H.ZIP, I.PHONE
    from     INDIVIDUALS I join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD
   where     H.STATE = 'CO' and I.NAME = 'John'; 

For relational databases, the HOUSEHOLD column in INDIVIDUALS should be an Omnidex index since it is a foreign key. For raw data files, the HOUSEHOLD column in HOUSEHOLDS should also be an Omnidex index since it is a primary key.

In this example, the STATE and COUNTY_CODE is a primary constraint in COUNTIES and a foreign constraint in HOUSEHOLDS.

  select     H.ADDRESS, H.CITY, H.STATE, H.ZIP
    from     HOUSEHOLDS H join COUNTIES C on H.STATE = C.STATE and
                                             H.COUNTY_CODE = C.COUNTY_CODE
   where     C.STATE = 'CO' and C.COUNTY = 'Boulder';

For relational databases, the STATE and COUNTY_CODE columns of HOUSEHOLD should be indexed using a multi-column Omnidex index. For raw data files, the STATE and COUNTY_CODE columns of COUNTIES should also be a multi-column Omnidex index. For all databases, the COUNTY column of COUNTIES should be a QuickText index.

This sample environment file shows the Omnidex indexes that will optimize these queries.

create environment
 in                   "simple.xml"
 with                 delete;

create database       "SIMPLE"
  type                FILE
  index_directory     "idx"
 in                   "simple.xml";

create table          "COUNTIES"
 physical             "dat/counties.dat"
 data_caching         none
 (
  "STATE"             CHARACTER(2),
  "COUNTY_CODE"       CHARACTER(3),
  "COUNTY"            STRING(31)        quicktext,
  constraint COUNTIES_STATE_PK primary ("STATE", "COUNTY_CODE"),
  omnidex "COUNTIES_PK" ("STATE", "COUNTY_CODE")
 )
 in                   "simple.xml";

create table          "HOUSEHOLDS"
 physical             "dat/households.dat"
 (
  "HOUSEHOLD"         CHARACTER(12)     omnidex,
  "ADDRESS"           CHARACTER(50)     quicktext,
  "CITY"              CHARACTER(28),
  "STATE"             CHARACTER(2)      omnidex,
  "ZIP"               CHARACTER(5),
  "COUNTY_CODE"       CHARACTER(3),
  "COUNTRY"           CHARACTER(2),
  constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"),
  omnidex "COUNTIES_FK" ("STATE", "COUNTY_CODE")
 )
 in                   "simple.xml";

create table          "INDIVIDUALS"
 physical             "dat/individuals.dat"
 (
  "INDIVIDUAL"        CHARACTER(12),
  "HOUSEHOLD"         CHARACTER(12)     omnidex,
  "NAME"              CHARACTER(50)     quicktext,
  "GENDER"            CHARACTER(1),
  "BIRTHDATE"         ANSI DATE,
  "PHONE"             CHARACTER(14),
  "EMAIL"             CHARACTER(60),
  constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"),
  constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS"
 )
 in                   "simple.xml";

Prev | Next

See also: