Overview | Data Caching | Qualification Caching | Filesystem Caching

Omnidex optimizes most statements by qualifying rows using the Omnidex indexes. Each of these qualifications searches one or more index and write index pointers to a temporary file. This is much faster than searching the data directly, but this approach can be further optimized.

Many applications issue several SQL statements to process a single request from a user. The ActiveCounts feature of Omnidex provides a classic example. In that example, the application performed three consecutive statements:

  select        STATE, count(*)
    from        LIST
    group by    STATE;
 
  select        GENDER, count(*)
    from        LIST
    where       STATE in ('AZ','CA','CO','NM','OR','UT','WA')
    group by    GENDER;

  select        EDUCATION, count(*)
    from        LIST
    where       STATE in ('AZ','CA','CO','NM','OR','UT','WA') and 
                GENDER = 'F'
    group by    EDUCATION;

In these three statements, the prior criteria was repeated and an additional piece of criteria.

If a Hashed Data Cache is used in a query, it can be seen using Omnidex query plans. The following query plan shows a “Build cache” step that builds the Hashed Data Cache, and then a subsequent “Retrieve” step that retrieves from the Hashed Data Cache.

----------------------------------- SUMMARY -----------------------------------
Select        INDIVIDUALS.NAME,
              GENDERS.DESCRIPTION
  from        INDIVIDUALS
  join        GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER;

Version:      5.2.01  (Compiled Feb  2 2012 21:29:57)
Warnings:     SEQUENTIAL_SCAN
----------------------------------- DETAILS -----------------------------------
Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2;
Retrieve INDIVIDUALS sequentially;
 Retrieve {1} using PK_GENDER = INDIVIDUALS.GENDER;
 Return INDIVIDUALS.NAME, GENDERS.DESCRIPTION;
-------------------------------------------------------------------------------

The Hashed Data Cache is limited to 32MB of memory per table by default. This can be adjusted using the HDC_THRESHOLD option of the SELECT statement, which sets the maximum cache size, measured in MB.

Prev | Next

See also: