Overview | Criteria | Joins | Subqueries | Distinct | Aggregations | Ordering

Criteria is the often the easiest aspect of a query to optimize. If all columns that are referenced in the WHERE clause are indexed, Omnidex will use those indexes to optimize the criteria. At the same time, there are advanced strategies that can help with more challenging situations.

Most Omnidex indexes are placed on a simple column; however, Omnidex indexes can also consist of multiple columns. This can be beneficial when certain combinations of criteria are repeated for a large percentage of queries. For example, an application might issue the following criteria for all or most of their queries:

select ... WHERE COUNTRY = 'US' and ACTIVE_CUSTOMER = 'Y' ...

Normally, Omnidex would first search the COUNTRY index, and then intersect with the ACTIVE_CUSTOMER index; however, if a multi-column index exists that includes both of those columns, Omnidex can resolve both criteria in one index search. This will save time, and can make a noticeable difference across an entire application.

This strategy makes the most sense in the following scenarios:

• The table is very large, with at least a million rows.
• The combination of criteria is used in a majority of queries.
• At least one of the columns has cardinality greater than 32, meaning that it will not be an Omnidex Bitmap index.

It is also typical that the columns are both indexed individually, and also indexed in a multi-column index. Although this causes redundant indexing, it allows criteria to be processed against the columns even when both columns are not referenced.

The easiest way to create a multi-column index is to declare it in the CREATE TABLE statement after the column declarations. In the example below, GENDER and BIRTHDATE are combined into a multi-column index.

create table                 "INDIVIDUALS"
 physical                    "dat\individuals*.dat"
 (
  "INDIVIDUAL"               CHARACTER(12)     omnidex,
  "HOUSEHOLD"                CHARACTER(12)     omnidex,
  "NAME"                     CHARACTER(50)     quicktext,
  "GENDER"                   CHARACTER(1)      omnidex bitmap,
  "BIRTHDATE"                ANSI DATE         omnidex,
  "PHONE"                    CHARACTER(14)     omnidex,
  "EMAIL"                    CHARACTER(60)     quicktext,
  omnidex GENDER_BIRTHDATE ("GENDER", "BIRTHDATE"),
  constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"),
  constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
  constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
 )
 in                          "simple.xml";

Prev | Next

See also: