The Informix Query Optimisation Process
Whenever a SELECT statement is executed, the
system has to decode it and decide whm strategy to use to produce the
requested rows. When analysing a statement, it looks at the
FROM clause and at the
WHERE clause.
It splits conditions in the WHERE clause into
two types:
Optimisation Process Filter conditions are ones which potentially restrict the number of rows selected from a table.
-
Join conditions relate columns from two different tables.
Five criteria are applied to each condition to decide which order should be used to process the tables:
Examine the FROM Clause for OUTER JOINS
If there is one, priority is given to dominant table(s) as this allows it
to ensure t hat rows from these tables are not discarded prematurely. If
there are several tables at the same level of priority after examining the
FROM clause, the other criteria are used to
determine the ordering.
The Indexes on Joining Columns
It is most efficient to search sequentially through the unindexed table, using the index to locate the joining rows, from the indexed table. If both are indexed:
One uniquely and the other not, then the non-unique index is searched and the unique index used to speed the search for joining rows.
On non-unique columns priority is given to the table where the index has fewer columns.
Auto-Indexing
If there is no way of choosing between two tables because none of the joining columns are indexed, the optimiser creates a temporary table which is a copy of the larger of the two tables. It also creates an advantageous index on this temporary table and uses this when answering the query. The temporary table is dropped when the query is complete. This process is called auto-indexing.
Filter Conditions
If the system cannot decide the ordering on the first two criteria because there was a single-column join between the two tables and both columns had a non-unique index, the filter conditions are considered:
If only one table has a filter condition, that table is given priority.
If both tables have indexes on the filter columns but only one of the indexes is unique, the table with the unique index is given priority.
If both tables have indexes and both tables are either unique or non-unique but the indexes do not cover the same number of columns, the table with the larger index is given priority.
FROM Clause Order
Occasionally, the system will still be unable to choose between the
two tables, in which case it gives priority to the table with the
fewest rows. If both tables have the same number of rows (for example,
if it is a self-join), the system will give priority to the table listed first
in the FROM clause.