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";
See also: