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 only makes sense for combinations of criteria that occur very regularly. Since the individual columns are usually indexed independently, these composite indexes amount to redundant indexing, requiring additional indexing time and disk space.

To create a multi-column index,

> create table          "HOUSEHOLDS"
>>  physical             "dat/households.dat"
>>  (
>>   "HOUSEHOLD"         CHARACTER(12)     omnidex,
>>   "ADDRESS"           CHARACTER(50)     quicktext,
>>   "CITY"              CHARACTER(28)     quicktext,
>>   "STATE"             CHARACTER(2)      omnidex,
>>   "ZIP"               CHARACTER(5)      omnidex,
>>   "COUNTRY"           CHARACTER(2)      omnidex,
>>   constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"),
>>   omnidex    STATE_CITY_ZIP ("STATE", "CITY", "ZIP") 
>>  )
>>  in                   "simple.xml";
Table HOUSEHOLDS created in simple.xml
>

Prev | Next

See also: