Administration: Omnidex Features

Rollup Tables

Overview | Design | Creation | Indexing | Optimization

Optimizing Queries using Rollup Tables

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.

Examples

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.

Simple count

----------------------------------- 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);
-------------------------------------------------------------------------------

Simple count with criteria

----------------------------------- 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);
-------------------------------------------------------------------------------

Simple count distinct

----------------------------------- 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);
-------------------------------------------------------------------------------

Simple count distinct with criteria

----------------------------------- 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);
-------------------------------------------------------------------------------

Simple grouped count

----------------------------------- 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);
-------------------------------------------------------------------------------

Simple grouped count with criteria

----------------------------------- 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);
-------------------------------------------------------------------------------

Count grouped by joined table

----------------------------------- 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);
-------------------------------------------------------------------------------

Count grouped by joined table with criteria

----------------------------------- 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);
-------------------------------------------------------------------------------

Prev

Additional Resources

Administration

Back to top

admin/features/rollups/optimization.txt · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes