Administration: Indexing Strategies

Basic Strategies

Overview | Criteria | Table Joins | Aggregations | Ordering

Optimizing Ordering

Ordering is usually optimized by creating an index containing all of the columns in the ORDER BY clause. The order of the column in the index must match the order of the columns in the ORDER BY clause. At present, only ascending ORDER BY clauses are optimized.

Example 1. Single-column ORDER BY clauses

In this example, the ORDER BY clause is optimized using the same index used for processing criteria.

  select     NAME, PHONE 
    from     INDIVIDUALS
    where    BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 1989' and NAME = 'John'
    order by BIRTHDATE;

The BIRTHDATE index will be used to satisfy both the criteria and the ORDER BY clause.

Example 2. Mixed use of QuickText and ORDER BY clauses

In this example, the ORDER BY clause is optimized using the same index used for processing criteria.

  select     NAME, PHONE 
    from     INDIVIDUALS
    where    NAME = 'John'
    order by NAME;

The NAME index is a QuickText index and can satisfy the criteria, but it cannot satisfy the ORDER BY because the words have been parsed. An additional Omnidex index on NAME is required to optimize the ORDER BY.

Example 3. Multi-column ORDER BY clauses

In this example, the ORDER BY clause will be optimized with a multi-column index.

  select     NAME, PHONE 
    from     INDIVIDUALS
    where    BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 1989' and NAME = 'John'
    order by GENDER, BIRTHDATE;

A multi-column Omnidex index containing GENDER and BIRTHDATE, in that order, will optimize this ORDER BY clause.

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),
  "STATE"             CHARACTER(2),
  "ZIP"               CHARACTER(5),
  "COUNTRY"           CHARACTER(2),
  constraint HSHD_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),
  "BIRTHDATE"         ANSI DATE         omnidex,
  "PHONE"             CHARACTER(14),
  "EMAIL"             CHARACTER(60),
  constraint IND_INDIVIDUAL_PK primary ("INDIVIDUAL"),
  constraint IND_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
  omnidex "ORDERBY_01" ("NAME"),
  omnidex "ORDERBY_02" ("GENDER", "BIRTHDATE")
 )
 in                   "simple.xml";

Prev