Overview | Anatomy of a Query Plan | Reading a Query Plan | Configuring Query Plans | Optimizing Queries

The basic optimization of a query with Omnidex has _ main steps. Most of the time, following these steps is enough to insure that the query performs well.

Omnidex has different indexing approaches based on the data model. Be sure to understand the parent-child relationships of tables by following their primary and foreign constraints. For more complex databases, it is often useful to diagram the data model.

For obvious reasons, Omnidex performance is affected by the cardinality of the table, meaning the number of rows in the table. This is especially true when large tables need to be joined together. Omnidex performance is also affected by the cardinality of the columns, meaning the number of unique values in the column. For example, columns with cardinalities of 32 or less are good candidates for Omnidex Bitmap indexes.

The query plan is the key to understanding the performance of the query. A quick look at the warnings and notes in the Summary section provides clues for optimizing the query.

This sample query has been run an a database with no Omnidex indexing at all. Moreover, this is a database of raw data files, and so there is no indexing in the underlying database, either.

----------------------------------- SUMMARY -----------------------------------
Select        I.GENDER,
              count(*)
  from        HOUSEHOLDS H
  join        INDIVIDUALS I on H.HOUSEHOLD = I.HOUSEHOLD
  where       ((H.STATE = 'CO' and
                H.CITY = 'Denver') or
               (H.STATE = 'AZ' and
                H.CITY = 'Phoenix')) and
              I.BIRTHDATE > 'January 1, 1980'
  group by    I.GENDER;

Version:      5.2.01  (Compiled Feb  2 2012 21:29:57)
Warnings:     UNOPTIMIZED_CRITERIA, UNOPTIMIZED_AGGREGATION, UNOPTIMIZED_SORT,
              SEQUENTIAL_SCAN, SEQUENTIAL_TABLE_JOIN
Notes:        Optimized aggregations are not possible because Omnidex indexes
                don't exist on all aggregated columns and GROUP BY columns and
                all links to dimension tables
              HDC optimization not used because table INDIVIDUALS has zero
                cardinality.
              SortMerge optimization not used because table HOUSEHOLDS has zero
                cardinality.
              Sequential Table Join on INDIVIDUALS with H.HOUSEHOLD =
                I.HOUSEHOLD
              Filter on column STATE will not be optimized because there is not
                an Omnidex index installed on the column.
              Filter on column CITY will not be optimized because there is not
                an Omnidex index installed on the column.
              Filter on column BIRTHDATE will not be optimized because there is
                not an Omnidex index installed on the column.
----------------------------------- DETAILS -----------------------------------
Retrieve HOUSEHOLDS H sequentially;
 Retrieve INDIVIDUALS I sequentially;
 Filter H.HOUSEHOLD = I.HOUSEHOLD;
 Filter I.BIRTHDATE > 'January 1, 1980';
 Filter H.STATE = 'CO';
 Filter H.CITY = 'Denver';
 Filter FILTER 0 AND FILTER 1;
 Filter H.STATE = 'AZ';
 Filter H.CITY = 'Phoenix';
 Filter FILTER 3 AND FILTER 4;
 Filter FILTER 2 OR FILTER 5;
 Pass to queue {1} [I.GENDER];
Sort {1} for GROUP BY [I.GENDER];
Retrieve {1} sequentially;
Return I.GENDER, COUNT('*');
-------------------------------------------------------------------------------

The Warnings section displays many warnings that show opportunities for improvement:

• UNOPTIMIZED_CRITERIA. At least one piece of criteria is not supported by an Omnidex index. The goal is always to have all criteria processed through the Omnidex indexes. This is especially true for queries that have table joins, aggregations or ordering, since Omnidex indexes cannot be used for these steps unless all criteria has been processed through Omnidex indexes.
• UNOPTIMIZED_AGGREGATION. The aggregations in this query cannot be satisfied using Omnidex indexes. This means that the data must be retrieved from the database and sorted. It is preferable to have the aggregation processed through Omnidex indexes.
• UNOPTIMIZED_SORT. Some aspect of this query required sorting at run time. This is not always an optimization issue, as sometimes it is required to sort intermediate results that were derived from Omnidex indexes. If sorting can be eliminated through the use of Omnidex indexes, it is an easy way to improve the query performance.
• SEQUENTIAL_SCAN. At least one table is being sequentially scanned. This is not always an optimization issue, especially if more than 10% of the table is to be ultimately retrieved. Often it is an indication that Omnidex indexes were not present, either for processing criteria or table joins.
• SEQUENTIAL_TABLE_JOIN. At least one table join required a sequential table join, where the second table is sequentially scanned for each and every row in the first table. This is the most inefficient approach to table joins and should always be avoided when possible. It is usually an indication that an UPDATE STATISTICS command has not be run to register the table cardinalities, and that indexes are not present on the join columns.

• Optimized aggregations are not possible because Omnidex indexes don't exist on all aggregated columns and GROUP BY columns and all links to dimension tables.
• HDC optimization not used because table INDIVIDUALS has zero cardinality.
• SortMerge optimization not used because table HOUSEHOLDS has zero cardinality.
• Sequential Table Join on INDIVIDUALS with H.HOUSEHOLD = I.HOUSEHOLD
• Filter on column STATE will not be optimized because there is not an Omnidex index installed on the column.
• Filter on column CITY will not be optimized because there is not an Omnidex index installed on the column. * Filter on column BIRTHDATE will not be optimized because there is not an Omnidex index installed on the column.*

Next

See also: