Overview | Data Caching | Qualification Caching | File Caches

Omnidex has several approaches to choose from when joining tables. Standard Omnidex indexes can be used to join tables, and pre-joined indexes can be used to accelerate joins between parents tabled and child tables. Omnidex can use the underlying database's indexes to perform table joins. Omnidex can use a sort/merge technique to join especially tables without indexes. Omnidex can also load smaller tables into a temporary, memory-resident, hashed data cache for extremely fast table joins.

The Hashed Data Cache is used when a smaller table will be repeatedly accessed many times. The classic example of this joining the fact table of a star schema to dimension or snowflake tables. Other examples include any time a table is being joined to small reference tables. In these situations, Omnidex indexes will be favored for resolving criteria; however, if the smaller table must be accessed to return data in the result set, then the Hashed Data Cache may be used.

A classic example occurs when joining an INDIVIDUALS table to several reference tables, one for GENDERS, one for MARITAL_STATUSES, and one for EDUCATION. Each of these reference tables has less then 20 rows, meaning that the same rows will be repeated accessed throughout the scan of the INDIVIDUALS table. Assuming two GENDERS — Male and Female — a scan of one million INDIVIDUALS would result in each GENDERS row being accessed approximately a half-million times.

The Hashed Data Cache will temporarily cache these smaller tables into memory and create hash indexes for the join column. This means that the underlying data will only be accessed once, and all other access will be memory-resident. This yields a tremendous gain in performance.

VERIFY

This temporary cache is unique to a connection, and only lasts the duration of the query. This means that the Hashed Data Cache will work fine in environments that use online updates.

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: