Overview | Criteria | Table Joins | Aggregations | Ordering

Aggregations are usually optimized by creating an index containing all of the columns in the GROUP BY clause and all of the columns referenced in COUNT, SUM, MIN, MAX and AVERAGE functions. It is preferable to have the columns in the GROUP BY clause precede the columns being aggregated.

The COUNT(*) aggregation is treated uniquely. The use of the asterisk does not equate to an individual column and does not expand the side of the index. In fact, Omnidex maintains counts at each step of the search, so simple COUNT(*) aggregations do not need specialized indexing.

Some applications will use GROUP BY clauses that consist of columns from a child table and columns from parent or grandparent tables. In these cases, the Omnidex index in the child should include all columns from the child table as well as the foreign keys that point to the parent table.

It is fine for one index to service multiple aggregations. This occurs when one index contains a superset of all of the columns for several queries. For example, an index on columns A, B, C and D would allow sums of D grouped by A, and also averages of D and C grouped by B and A. The performance of these aggregations will begin to degrade as the index width increases, so indexes should not contain an excessive number of columns. A good rule of thumb is to keep the width of these indexes below 64 bytes, though they can be as large as 240 bytes.

In this example, the COUNT(*) does not require any additional indexes.

  SELECT     COUNT(*)
    FROM     INDIVIDUALS
    WHERE    GENDER = 'M' AND NAME = 'John';

The GENDER and NAME indexes which were created to satisfy the criteria will also satisfy the requested count.

In this example, the COUNT(*) does not require any additional indexes, but the GROUP BY clause requires an index.

  SELECT     GENDER, COUNT(*)
    FROM     INDIVIDUALS
    WHERE    BIRTHDATE = 'Jan 10, 1986' AND NAME = 'John'
    GROUP BY GENDER;

The BIRTHDATE and NAME columns must be indexed to satisfy the criteria and the GENDER column must be indexed to satisfy the GROUP BY clause. As discussed earlier, GENDER is a low-cardinality column and can be an Omnidex Bitmap index.

In this example, the GROUP BY clause contains columns from multiple parents.

  SELECT     C.DESCRIPTION, S.DESCRIPTION, H.CITY, COUNT(DISTINCT H.ZIP)
    FROM     HOUSEHOLDS H JOIN COUNTRIES C ON H.COUNTRY = C.COUNTRY 
                          JOIN STATES S ON H.STATE = S.STATE
    GROUP BY C.DESCRIPTION, S.DESCRIPTION, H.CITY;

This statement is indexed by creating a multi-column Omnidex index on the COUNTRY, STATE, CITY and ZIP columns in HOUSEHOLDS. The COUNTRY and STATE columns are included because they are foreign keys that correlate to the COUNTRIES.DESCRIPTION and STATES.DESCRIPTION columns. The CITY column is included because it is also in the GROUP BY clause. The ZIP column is included because it is referenced in an aggregation.

On relational databases, the primary keys for COUNTRIES and STATES do not need to be indexed, but on non-relational databases, they must be indexed as well.

In this example, the both SQL statements can be satisfied by one Omnidex index.

  SELECT     GENDER, BIRTHDATE, COUNT(DISTINCT HOUSEHOLD)
    FROM     INDIVIDUALS
    GROUP BY GENDER, BIRTHDATE;

  SELECT     HOUSEHOLD, GENDER, COUNT(*)
    FROM     INDIVIDUALS
    GROUP BY HOUSEHOLD, GENDER;

A multi-column Omnidex index containing GENDER, BIRTHDATE and HOUSEHOLD would satisfy both of these statements because all GROUP BY columns and all aggregated columns can be found in this index.

This sample environment file shows the Omnidex indexes that will optimize these queries.

CREATE environment
 IN                   "simple.xml"
 WITH                 DELETE;
 
CREATE DATABASE       "SIMPLE"
 TYPE                 FLATFILE
 index_directory      "idx"
 IN                   "simple.xml";
 
CREATE TABLE          "COUNTRIES"
 physical             "dat\cnt.dat"
 (
  "COUNTRY"           CHARACTER(2)      omnidex,
  "DESCRIPTION"       STRING(47),
  "LATITUDE"          FLOAT                          usage "LATITUDE",
  "LONGITUDE"         FLOAT                          usage "LONGITUDE",
  "CAPITAL"           STRING(31),
  "CAPITAL_LAT"       FLOAT                          usage "LATITUDE",
  "CAPITAL_LONG"      FLOAT                          usage "LONGITUDE",
  CONSTRAINT COUNTRIES_COUNTRY_PK PRIMARY ("COUNTRY")
 )
 IN                   "simple.xml";
 
CREATE TABLE          "STATES"
 physical             "dat\sta.dat"
 (
  "STATE"             CHARACTER(2)      omnidex,
  "DESCRIPTION"       STRING(31),
  "STATE_CODE"        CHARACTER(2),
  "REGION"            CHARACTER(2),
  "COUNTRY"           CHARACTER(2),
  "TAX_RATE"          FLOAT,
  CONSTRAINT STATES_STATE_PK PRIMARY ("STATE"),
  CONSTRAINT STATES_COUNTRY_FK FOREIGN ("COUNTRY") REFERENCES "COUNTRIES"
 )
 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"),
  CONSTRAINT HSHD_STATE_FK FOREIGN ("STATE") REFERENCES "STATES",
  CONSTRAINT HSHD_COUNTRY_FK FOREIGN ("COUNTRY") REFERENCES "COUNTRIES",
  omnidex "AGGREGATION_01" ("COUNTRY", "STATE", "CITY", "ZIP")
 )
 IN                   "simple.xml";
 
CREATE TABLE          "INDIVIDUALS"
 physical             "dat\individuals.dat"
 (
  "INDIVIDUAL"        CHARACTER(12),
  "HOUSEHOLD"         CHARACTER(12),
  "NAME"              CHARACTER(50)     quicktext,
  "GENDER"            CHARACTER(1)      omnidex bitmap,
  "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 "AGGREGATION_01" ("GENDER", "BIRTHDATE", "HOUSEHOLD"),
 )
 IN                   "simple.xml";

Prev | Next

See also: