Using this statement, the user can inform himself of the strategy applied for the execution of the specified select statement.
The following overview shows the strategies which are distinguished:
- TABLE SCAN
Sequential search through the complete table.
- SINGLE INDEX COLUMN USED (INDEX SCAN)
Sequential search through the complete specified single-column index.
- MULTIPLE INDEX COLUMN USED (INDEX SCAN)
Sequential search through the complete named multiple-column index.
- RANGE CONDITION FOR KEY COLUMN
Sequential search through a part of the table.
- EQUAL CONDITION FOR KEY COLUMN
The table has only one key column to which an EQUAL condition is applied. The corresponding table rows are directly accessed.
- IN CONDITION FOR KEY COLUMN
The table has only one key column to which an IN condition is applied. The corresponding table rows are directly accessed.
- EQUAL CONDITION FOR INDEXED COLUMN
An EQUAL condition is applied to a single-column indexed column. The related index list is used to directly access the corresponding table rows.
- IN CONDITION FOR INDEXED COLUMN
An IN condition is applied to a single-column indexed column. The related index lists are used to directly access the corresponding table rows.
- RANGE CONDITION FOR INDEXED COLUMN
A range condition is applied to a single-column indexed column. The index lists within the specified range are used to directly access the corresponding table rows.
- 'ORDER BY' VIA INDEXED COLUMN
No strategy better than the sequential search has been found. The index of the column specified after 'ORDER BY' is utilized.
- EQUAL CONDITION FOR MULTIPLE INDEX
An equality condition is applied to every column of the named multiple-column index. The related index list is used to directly access the corresponding table rows.
- RANGE CONDITION FOR MULTIPLE INDEX
There are equality or range conditions which are applied to the first k columns of a named multiple-column index. The index lists within the specified range are used to directly access the corresponding table rows.
- 'ORDER BY' VIA MULTIPLE INDEX
No strategy better than the sequential search has been found. The multiple-column index is used; the columns of this index were specified after 'ORDER BY' in correct sequence and order (ASC/DESC).
- INTERSECTION OF COLUMN INDEXES
There are several equality or range conditions which are applied to several single-column indexed columns. The intersection of the respective index lists is formed. The intersection index list is used to directly access the corresponding table rows.
- DIFFERENT STRATEGIES FOR OR TERMS
The analysis of the conditions combined by AND has not produced a strategy better than the sequential search. Conditions combined by OR have been transformed and analyzed, giving the result that various strategies have been found for the different elements of the search condition. Each strategy is displayed according to the above mentioned rules.
- CATALOG SCAN
A sequential search is performed on the catalog.
- CATALOG SCAN USING USER EQUAL CONDITION
A sequential search is performed through the catalog entries describing the objects of the identified user.
- CATALOG KEY ACCESS
The qualification contains equal conditions. This allows the query to be processed by accessing the key columns in the catalog (e.g., equal conditions for OWNER and TABLENAME in queries performed on DOMAIN.TABLES).
- NO STRATEGY NOW (ONLY AT EXECUTION TIME)
The corresponding column values in a correlated subquery are only known at the subquery's execution time. The strategy for the most effective access to the corresponding table of the subquery will not be determined until these values are available.
Certain selects are so complicated that they are divided into several internal select steps. This is indicated in the EXPLAIN output by several output lines and by the indication 'INTERNAL TEMPORARY RESULT' displayed as the table name. Only a sequential search is possible on such internal temporary results.
The EXPLAIN statement is described in the Reference manual.