* [[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]] ---- ==== Indexing Rollup Tables ==== Once the rollup tables have been created and declared, they may be indexed using Omnidex indexing. There are no restrictions on indexing of rollup tables, and rollup tables can be indexed just like any other table. Since rollup tables are often quite small, it is common to heavily index the rollup table. Ultimately, it is only necessary to index the rollup table in such a way that all queries that are redirected to the rollup table are fully optimized. This can be determined by reviewing the query plan for each query that is redirected to the rollup table. This is discussed in more detail later in this document. The following example shows indexing for the tables that were created in the previous page. <code> create table "INDIVIDUALS" physical "dat/individuals.dat" ( "INDIVIDUAL" CHARACTER(12) omnidex, "HOUSEHOLD" CHARACTER(12) omnidex, "NAME" CHARACTER(50) quicktext, "GENDER" CHARACTER(1) omnidex bitmap, "BIRTHDATE" ANSI DATE omnidex, "PHONE" CHARACTER(14) omnidex, "EMAIL" CHARACTER(60) quicktext, constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", ) in "simple.xml"; create table "INDIVIDUALS_BY_HOUSEHOLD" physical "dat/individuals_by_household.dat" ( "HOUSEHOLD" CHARACTER(12) omnidex, "GENDER" CHARACTER(1) omnidex bitmap, "BIRTHDATE" ANSI DATE omnidex, "NUM_INDIVIDUALS" UNSIGNED INTEGER, constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", ) as "select HOUSEHOLD, GENDER, BIRTHDATE, count(*) NUM_INDIVIDUALS from INDIVIDUALS group by HOUSEHOLD, GENDER, BIRTHDATE" in "simple.xml"; create table "INDIVIDUALS_BY_DEMO" physical "dat/individuals_by_demo.dat" ( "GENDER" CHARACTER(1) omnidex bitmap, "BIRTHDATE" ANSI DATE omnidex, "NUM_INDIVIDUALS" UNSIGNED INTEGER, constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", ) as "select GENDER, BIRTHDATE, count(*) NUM_INDIVIDUALS from INDIVIDUALS group by GENDER, BIRTHDATE" in "simple.xml"; </code> Indexes on rollup tables are automatically updated when the UPDATE INDEXES statement is issued: <code> > update indexes Database Table Rows CPU Elapsed ---------------------------------------------------------------------------- SIMPLE COUNTRIES 239 0:00 0:00 STATES 76 0:00 0:00 GENDERS 2 0:00 0:00 HOUSEHOLDS 1,909 0:00 0:00 INDIVIDUALS 5,000 0:00 0:00 INDIVIDUALS_BY_HOUSEHOLD 5,000 0:00 0:00 INDIVIDUALS_BY_DEMO 4,793 0:00 0:00 ---------------------------------------------------------------------------- Total 0:01 0:03 Omnidex indexes updated > </code> ==== ==== **[[admin:features:rollups:create|Prev]]** | **[[admin:features:rollups:optimization|Next]]** ====== Additional Resources ====== {{page>:admin:features:see_also&nofooter&noeditbtn}} {{page>:bottom_add&nofooter&noeditbtn}}

 
Back to top
admin/features/rollups/indexing.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