This is an old revision of the document!
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 show 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) -------------------------------------------------------------------------------