Overview | Design | Creation | Indexing | Optimization

Omnidex will directly use Rollup Tables to optimize queries. This means that the queries themselves do not need to be changed. When rollup tables are declared, their relationship to the underlying data is understood. This allows Omnidex to look at queries against the underlying data and seamlessly query the rollup table. This design allows the Omnidex Administrator to create and deploy rollup tables based on the changing needs of the queries, much the same way that indexes are added as needed. Developers simply write applications against the underlying database, yet automatically enjoy the improved performance from the rollup table.

It is possible to directly query the rollup table, but it is generally not recommended. The rollup table is a legitimate table in the environment, but once applications are written that rely on the rollup table, it becomes more difficult to change the rollups to meet the changing needs of the queries.

The following are examples of queries that can be redirected to the rollup table using the example shown earlier. The query plan is shown on each of these to illustrate how the redirection to the rollup table occurs. Note that these examples only aggregate counts, but any SQL aggregation function can be used.

 ----------------------------------- SUMMARY -----------------------------------
Select        count(*) \
  from        LIST

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: AGGREGATION, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Aggregate LIST_GEO using GEO02 for SUM(NUM_INDIVIDUALS) on 1
Return CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
-------------------------------------------------------------------------------

----------------------------------- SUMMARY -----------------------------------
Select        count(*) \
  from        LIST \
  where       CITY = 'Boulder'

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: MDKQUAL, AGGREGATION, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET
Aggregate LIST_GEO using GEO02 for SUM(NUM_INDIVIDUALS) on 1
Return CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
-------------------------------------------------------------------------------

----------------------------------- SUMMARY -----------------------------------
Select        count(distinct STATE) \
  from        LIST

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: AGGREGATION, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Aggregate LIST_GEO using GEO02 for COUNT(DISTINCT STATE) on 1
Return COUNT(DISTINCT LIST_GEO.STATE)
-------------------------------------------------------------------------------

----------------------------------- SUMMARY -----------------------------------
Select        count(distinct STATE) \
  from        LIST \
  where       CITY = 'Boulder'

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: MDKQUAL, AGGREGATION, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET
Aggregate LIST_GEO using GEO02 for COUNT(DISTINCT STATE) on 1
Return COUNT(DISTINCT LIST_GEO.STATE)
-------------------------------------------------------------------------------

----------------------------------- SUMMARY -----------------------------------
Select        STATE, \
              count(*) \
  from        LIST \
  group by    STATE

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: AGGREGATION, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1
Return LIST_GEO.STATE, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
-------------------------------------------------------------------------------

----------------------------------- SUMMARY -----------------------------------
Select        STATE, \
              count(*) \
  from        LIST \
  where       CITY = 'Boulder' \
  group by    STATE

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: MDKQUAL, AGGREGATION, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET
Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1
Return LIST_GEO.STATE, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
-------------------------------------------------------------------------------

----------------------------------- SUMMARY -----------------------------------
Select        STATES.DESCRIPTION, \
              count(*) \
  from        LIST \
  join        STATES on LIST.STATE = STATES.STATE \
  group by    STATES.DESCRIPTION

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: AGGREGATION, STARSCHEMA, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Build cache {C1} as (SELECT DESCRIPTION, STATE FROM STATES)
Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1
 Retrieve {C1} using PK_STATE = LIST_GEO.STATE
  Pass to queue {1} [STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)]
Sort {1} for GROUP BY [STATES.DESCRIPTION ASC]
Retrieve {1} sequentially
Return STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
-------------------------------------------------------------------------------
 

----------------------------------- SUMMARY -----------------------------------
Select        STATES.DESCRIPTION, \
              count(*) \
  from        LIST \
  join        STATES on LIST.STATE = STATES.STATE \
  where       CITY = 'Boulder' \
  group by    STATES.DESCRIPTION

Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
Optimization: MDKQUAL, AGGREGATION, STARSCHEMA, ROLLUP
Notes:        Table LIST translated to Rollup Table LIST_GEO
----------------------------------- DETAILS -----------------------------------
Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET
Build cache {C1} as (SELECT DESCRIPTION, STATE FROM STATES)
Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1
 Retrieve {C1} using PK_STATE = LIST_GEO.STATE
  Pass to queue {1} [STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)]
Sort {1} for GROUP BY [STATES.DESCRIPTION ASC]
Retrieve {1} sequentially
Return STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
-------------------------------------------------------------------------------

Prev