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

----------------------------------- 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 Jan 23 2012 17:27:25)
Warnings:     UNOPTIMIZED_CRITERIA, UNOPTIMIZED_AGGREGATION, UNOPTIMIZED_SORT,
              SEQUENTIAL_SCAN, SEQUENTIAL_TABLE_JOIN
Notes:        Optimized aggregations are not possible because no ASK index
                contains all 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 MDK index installed on the column.
              Filter on column CITY will not be optimized because there is not
                an MDK index installed on the column.
              Filter on column BIRTHDATE will not be optimized because there is
                not an MDK 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('*');
-------------------------------------------------------------------------------
>

A plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, any optimization settings or warnings that affect the query, and even occasional suggestions for optimizing the query. The Details section shows the steps that will be executed to satisfy the query. Steps are executed in the order displayed, and are indented as needed to show loops of instructions.

In the simple example above, three columns are retrieved from the INDIVIDUALS table. Since no criteria was provided in a WHERE clause, the details of the plan show that the table is retrieved sequentially and the columns are returned. A simple warning indicates that a sequential scan is taking place, which in this case cannot be avoided.

Next

See also: