Administration: Indexing Strategies

Basic Strategies

Overview | Criteria | Table Joins | Aggregations | Ordering

Optimizing Criteria

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.

Example 1. Basic Criteria

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.

Example 2. Textual Criteria

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.

Example 3. Low-cardinality Criteria

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.

Sample Environment File

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";

Prev | Next