Overview | Criteria | Table Joins | Aggregations | Ordering
Criteria is usually optimized by creating indexes on each column involved in the WHERE clause of a SQL statement. This is true regardless of the use of Boolean operators or parentheses. Normally, these will be installed with basic Omnidex indexes; however, some columns may contain textual data and would benefit from QuickText indexes.
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 the following statement, look for the columns used as criteria in the WHERE clause
select HOUSEHOLD, ADDRESS, CITY, STATE, ZIP from HOUSEHOLDS where ((STATE = 'CO' and CITY = 'Boulder') or (STATE = 'IL' and CITY = 'Chicago'));
The STATE and CITY columns should be Omnidex indexes.
In this example, you might want to use QuickText indexes. QuickText indexes will parse and index each word in the column and will allow them to be searched case-insensitively.
select INDIVIDUAL, NAME, PHONE from INDIVIDUALS where BIRTHDATE = 'Jan 10, 1986' and NAME = 'John';
The BIRTHDATE column should be an Omnidex index and the NAME column should be a Quicktext index.
If a column has low cardinality, meaning that it has less than 32 distinct values, it should be indexed as an Omnidex Bitmap index. This improves performance and saves disk space.
select INDIVIDUAL, NAME, PHONE from INDIVIDUALS where GENDER = 'M' and NAME = 'John';
The GENDER column should be an Omnidex Bitmap index, and the NAME column 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 "HOUSEHOLDS" physical "dat/households.dat" ( "HOUSEHOLD" CHARACTER(12), "ADDRESS" CHARACTER(50), "CITY" CHARACTER(28) quicktext, "STATE" CHARACTER(2) omnidex, "ZIP" CHARACTER(5), "COUNTRY" CHARACTER(2), constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD") ) in "simple.xml"; create table "INDIVIDUALS" physical "dat/individuals.dat" ( "INDIVIDUAL" CHARACTER(12), "HOUSEHOLD" CHARACTER(12), "NAME" CHARACTER(50) quicktext, "GENDER" CHARACTER(1) omnidex bitmap, "BIRTHDATE" ANSI DATE omnidex, "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: