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.
----------------------------------- SUMMARY ----------------------------------- Select count(*) from INDIVIDUALS; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Aggregate INDIVIDUALS_BY_DEMO using NUM_INDIVIDUALS for SUM(NUM_INDIVIDUALS); Return CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); -------------------------------------------------------------------------------
----------------------------------- SUMMARY ----------------------------------- Select count(*) from INDIVIDUALS where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000'; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan 1, 1980"' and '"Dec 31, 2000"'; Aggregate INDIVIDUALS_BY_DEMO using NUM_INDIVIDUALS for SUM(NUM_INDIVIDUALS); Return CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); -------------------------------------------------------------------------------
----------------------------------- SUMMARY ----------------------------------- Select count(distinct GENDER) from INDIVIDUALS; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Aggregate INDIVIDUALS_BY_DEMO using GENDER for COUNT(DISTINCT GENDER) on 1; Return COUNT(DISTINCT INDIVIDUALS_BY_DEMO.GENDER); -------------------------------------------------------------------------------
----------------------------------- SUMMARY ----------------------------------- Select count(distinct GENDER) from INDIVIDUALS where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000'; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan 1, 1980"' and '"Dec 31, 2000"'; Aggregate INDIVIDUALS_BY_DEMO using GENDER for COUNT(DISTINCT GENDER) on 1; Return COUNT(DISTINCT INDIVIDUALS_BY_DEMO.GENDER); -------------------------------------------------------------------------------
----------------------------------- SUMMARY ----------------------------------- Select GENDER, count(*) from INDIVIDUALS group by GENDER; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), SUM(NUM_INDIVIDUALS); Return INDIVIDUALS_BY_DEMO.GENDER, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); -------------------------------------------------------------------------------
----------------------------------- SUMMARY ----------------------------------- Select GENDER, count(*) from INDIVIDUALS where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000' group by GENDER; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan 1, 1980"' and '"Dec 31, 2000"'; Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), SUM(NUM_INDIVIDUALS); Return INDIVIDUALS_BY_DEMO.GENDER, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); -------------------------------------------------------------------------------
----------------------------------- SUMMARY ----------------------------------- Select GENDERS.DESCRIPTION, count(*) from INDIVIDUALS join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER group by GENDERS.DESCRIPTION; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2; Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), SUM(NUM_INDIVIDUALS) on 1; Retrieve {1} using PK_GENDER = INDIVIDUALS_BY_DEMO.GENDER; Pass to queue {2} [GENDERS.DESCRIPTION, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER)]; Sort {2} for GROUP BY [GENDERS.DESCRIPTION]; Retrieve {2} sequentially; Return GENDERS.DESCRIPTION, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); -------------------------------------------------------------------------------
----------------------------------- SUMMARY ----------------------------------- Select GENDERS.DESCRIPTION, count(*) from INDIVIDUALS join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000' group by GENDERS.DESCRIPTION; Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO ----------------------------------- DETAILS ----------------------------------- Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan 1, 1980"' and '"Dec 31, 2000"' on 1; Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2; Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), SUM(NUM_INDIVIDUALS) on 1; Retrieve {1} using PK_GENDER = INDIVIDUALS_BY_DEMO.GENDER; Pass to queue {2} [GENDERS.DESCRIPTION, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER)]; Sort {2} for GROUP BY [GENDERS.DESCRIPTION]; Retrieve {2} sequentially; Return GENDERS.DESCRIPTION, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); -------------------------------------------------------------------------------