This shows you the differences between two versions of the page.
|
admin:features:rollups:optimization [2011/01/10 22:45] els created |
admin:features:rollups:optimization [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Omnidex Features ====== | ||
| - | |||
| - | ===== Rollup Tables ===== | ||
| - | |||
| - | [[admin:features:rollups:home|Overview]] -> [[admin:features:rollups:design|Design]] -> [[admin:features:rollups:create|Creation]] -> [[admin:features:rollups:indexing|Indexing]] -> **[[admin:features:rollups:optimization|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 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. | ||
| - | |||
| - | |||
| - | === Simple count === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | </code> | ||
| - | |||
| - | === Simple count with criteria === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | </code> | ||
| - | |||
| - | === Simple count distinct === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | |||
| - | </code> | ||
| - | |||
| - | === Simple count distinct with criteria === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | |||
| - | </code> | ||
| - | |||
| - | === Simple grouped count === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | |||
| - | </code> | ||
| - | |||
| - | === Simple grouped count with criteria === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | |||
| - | </code> | ||
| - | |||
| - | === Count grouped by joined table === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | |||
| - | </code> | ||
| - | |||
| - | === Count grouped by joined table with criteria === | ||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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) | ||
| - | ------------------------------------------------------------------------------- | ||
| - | |||
| - | </code> | ||
| - | |||
| - | ==== ==== | ||
| - | **[[admin:features:rollups:indexing|Prev]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | {{page>:admin:features:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||