Specifying a Query Plan with PLAN
Go Up to Understanding Data Retrieval with SELECT
To process a SELECT statement, InterBase uses an internal algorithm, called the query optimizer, to determine the most efficient plan for retrieving data. The most efficient retrieval plan also results in the fastest retrieval time. Occasionally the optimizer may choose a plan that is less efficient. For example, when the number of rows in a table grows sufficiently large, or when many duplicate rows are inserted or deleted from indexed columns in a table, but the selectivity of the index is not recomputed, the optimizer might choose a less efficient plan.
For these occasions, SELECT provided an optional PLAN clause that enables a knowledgeable programmer to specify a retrieval plan. A query plan is built around the availability of indexes, the way indexes are joined or merged, and a chosen access method.
To specify a query plan, use the following PLAN syntax:
PLAN <plan_expr>
<plan_expr> =
[JOIN | [SORT] MERGE] (<plan_item> | <plan_expr>
[, <plan_item> | <plan_expr> ...])
<plan_item> = {table | alias}
NATURAL | INDEX ( <index> [, <index> ...]) | ORDER <index>
The PLAN syntax enables specifying a single table, or a join of two or more tables in a single pass. Plan expressions can be nested in parentheses to specify any combination of joins.
During retrieval, information from different tables is joined to speed retrieval. If indexes are defined for the information to be joined, then these indexes are used to perform a join. The optional JOIN keyword can be used to document this type of operation. When no indexes exist for the information to join, retrieval speed can be improved by specifying SORT MERGE instead of JOIN.
A plan_item is the name of a table to search for data. If a table is used more than once in a query, aliases must be used to distinguish them in the PLAN clause. Part of the plan_item specification indicates the way that rows should be accessed. The following choices are possible:
NATURAL, the default order, specifies that rows are accessed sequentially in no defined order. For unindexed items, this is the only option.INDEXspecifies that one or more indexes should be used to access items. All indexes to be used must be specified. If any Boolean or join terms remain after all indexes are used, they will be evaluated without benefit of an index. If any indexes are specified that cannot be used, an error is returned.ORDERspecifies that items are to be sorted based on a specified index.