Overview | Design | Creation | Indexing | Optimization

Once the rollup tables have been created and declared, they may be indexed using Omnidex indexing. There are no restrictions on indexing of rollup tables, and rollup tables can be indexed just like any other table.

Since rollup tables are often quite small, it is common to heavily index the rollup table. Ultimately, it is only necessary to index the rollup table in such a way that all queries that are redirected to the rollup table are fully optimized. This can be determined by reviewing the query plan for each query that is redirected to the rollup table. This is discussed in more detail later in this document.

The following example shows indexing for the tables that were created in the previous page.

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,
  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";

create table          "INDIVIDUALS_BY_HOUSEHOLD"
 physical             "dat/individuals_by_household.dat"
 (
  "HOUSEHOLD"         CHARACTER(12)     omnidex,
  "GENDER"            CHARACTER(1)      omnidex bitmap,
  "BIRTHDATE"         ANSI DATE         omnidex,
  "NUM_INDIVIDUALS"   UNSIGNED INTEGER,
  constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
  constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
 )
 as "select           HOUSEHOLD,
                      GENDER,
                      BIRTHDATE,
                      count(*)  NUM_INDIVIDUALS
       from           INDIVIDUALS
       group by       HOUSEHOLD,
                      GENDER,
                      BIRTHDATE"
 in                   "simple.xml";


create table          "INDIVIDUALS_BY_DEMO"
 physical             "dat/individuals_by_demo.dat"
 (
  "GENDER"            CHARACTER(1)      omnidex bitmap,
  "BIRTHDATE"         ANSI DATE         omnidex,
  "NUM_INDIVIDUALS"   UNSIGNED INTEGER,
  constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
 )
 as "select           GENDER,
                      BIRTHDATE,
                      count(*)  NUM_INDIVIDUALS
       from           INDIVIDUALS
       group by       GENDER,
                      BIRTHDATE"
 in                   "simple.xml";

Indexes on rollup tables are automatically updated when the UPDATE INDEXES statement is issued:

> update indexes
Database
 Table                                       Rows        CPU    Elapsed
----------------------------------------------------------------------------
SIMPLE
 COUNTRIES                                    239       0:00       0:00
 STATES                                        76       0:00       0:00
 GENDERS                                        2       0:00       0:00
 HOUSEHOLDS                                 1,909       0:00       0:00
 INDIVIDUALS                                5,000       0:00       0:00
 INDIVIDUALS_BY_HOUSEHOLD                   5,000       0:00       0:00
 INDIVIDUALS_BY_DEMO                        4,793       0:00       0:00
----------------------------------------------------------------------------
Total                                                   0:01       0:03

Omnidex indexes updated
>

Prev | Next