A PCRE internal error occured. This might be caused by a faulty plugin

* [[topbar |Table of Contents]] * [[intro:overview:what|Introduction]] * [[install:home|Installation]] * [[install:editions|Product Editions]] * [[install:requirements|System Requirements]] * [[install:platforms|Supported Platforms]] * [[install:guides:home|Installation Guides]] * [[install:licensing:home|Licensing]] * [[install:releases:home|Release Notes]] * [[admin:home|Administration]] * [[admin:admin:home|Administration Basics]] * [[admin:admin:architecture:home|Omnidex Architecture]] * [[admin:admin:applications:home|Building Applications]] * [[admin:basics:home|Omnidex Fundamentals]] * [[admin:basics:environments:home|Omnidex Environments]] * [[admin:basics:sqlengine:home|Omnidex SQL Engine]] * [[admin:basics:updates:home|Omnidex Updates]] * [[admin:basics:network:home|Network Services]] * [[admin:indexing:home|Omnidex Indexing]] * [[admin:indexing:concepts:home|Indexing Concepts]] * [[admin:indexing:indexes:home|Indexing Options]] * [[admin:indexing:creation:home|Index Creation]] * [[admin:indexing:strategies:home|Indexing Strategies]] * [[admin:indexing:activecounts:home|ActiveCounts]] * [[admin:indexing:powersearch:home|PowerSearch]] * [[admin:indexing:autocomplete:home|AutoComplete]] * [[admin:indexing:text:home|Omnidex Text]] * [[admin:features:home|Omnidex Features]] * [[admin:features:snapshots:home|Omnidex Snapshots]] * [[admin:features:grids:home|Omnidex Grids]] * [[admin:features:rollups:home|Rollup Tables]] * [[admin:features:geo:home|Geographic Searches]] * [[admin:features:expressioncols:home|Expression Columns]] * [[admin:features:segments:home|Omnidex Segments]] * [[admin:features:attach:home|Attaching Data]] * [[admin:features:export:home|Exporting Data]] * [[admin:optimization:home|Optimizing Queries]] * [[admin:optimization:plans:home|Query Plans]] * [[admin:optimization:caches:home|Dynamic Caches]] * [[admin:optimization:config:home|Configuring Omnidex]] * [[integration:home|Integration]] * [[integration:rdbms:home|Relational Databases]] * [[integration:rdbms:oracle:home|Oracle]] * [[integration:rdbms:sqlserver:home|SQL Server]] * [[integration:rdbms:mysql:home|MySQL]] * [[integration:rdbms:odbc:home|Generic ODBC]] * [[integration:rawdata:home|Raw Data Files]] * [[integration:rawdata:fixed:home|Fixed-length Files]] * [[integration:rawdata:delimited:home|Delimited Files]] * [[integration:rawdata:ost:home|Standalone Tables]] * [[dev:home|Development]] * [[dev:sql:home|Omnidex SQL]] * [[dev:sql:overview|Overview]] * [[dev:sql:statements:home|Statements]] * [[dev:sql:functions:home|Functions]] * [[dev:sql:examples:home|Examples]] * [[dev:connections:home|Connection Strings]] * [[dev:odbc:home|ODBC Interface]] * [[dev:jdbc:home|JDBC Interface]] * [[dev:debugging:home|Logging/Debugging]] * [[programs:home|Programs]] * [[programs:odxsql:home|OdxSQL]] * [[programs:odxnet:home|OdxNet]] * [[appendix:home|Appendix]] * [[dev:sql:home|Omnidex SQL]] * [[dev:sql:statements:home|Statements]] * [[dev:sql:functions:home|Functions]] * [[dev:sql:examples:home|Examples]] * [[http://www.omnidex.com/download/|Downloads]] * [[support:home|Support]]

~~NOTOC~~ {{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 shown earlier. The query plan is shown on each of these to illustrate how the redirection to the rollup table occurs. === Simple count === <code> ----------------------------------- 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); ------------------------------------------------------------------------------- </code> === Simple count with criteria === <code> ----------------------------------- 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); ------------------------------------------------------------------------------- </code> === Simple count distinct === <code> ----------------------------------- 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); -------------------------------------------------------------------------------</code> === Simple count distinct with criteria === <code> ----------------------------------- 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); ------------------------------------------------------------------------------- </code> === Simple grouped count === <code> ----------------------------------- 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); ------------------------------------------------------------------------------- </code> === Simple grouped count with criteria === <code> ----------------------------------- 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); ------------------------------------------------------------------------------- </code> === Count grouped by joined table === <code> ----------------------------------- 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); ------------------------------------------------------------------------------- </code> === Count grouped by joined table with criteria === <code> ----------------------------------- 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); ------------------------------------------------------------------------------- </code> ==== ==== **[[admin:features:rollups:indexing|Prev]]** ====== Additional Resources ====== {{page>:admin:features:see_also&nofooter&noeditbtn}} {{page>:bottom_add&nofooter&noeditbtn}}

 
Back to top
admin/features/rollups/optimization.txt ยท Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0