Overview | Criteria | Table Joins | Aggregations | 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.
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.
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.
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.
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";
See also: