The following conditions can be utilized for the selection of a search strategy:
<column spec> = | < | <= | > | >= <value expr>
<column spec> BETWEEN <value expr> AND <value expr>
<column spec> LIKE <value expr>
<column spec> IN <value list>
The IN condition can only be utilized when the <column spec> refers to the only key column or to a column for which a single-column index is available (see Section Definition of Terms ).
Conditions like
NOT (<value expression> <comp op> <value expression>)
are transformed, if possible, into an expression
<value expression> negated<comp op> <value expression> ,
and then processed in this format. If conditions cannot be transformed into one of the above mentioned formats, they cannot be used for the selection of a search strategy.
The order of conditions combined by equivalent Boolean operators has no influence on the selection of a search strategy.
In principle, every search can be performed sequentially through the entire table. This has to be done whenever
- no (<search condition>) is specified,
- no condition is specified either for key columns or for indexed columns.
If the possible non-sequential search strategies are more costly than the sequential search, the table is processed sequentially.
When conditions for key columns exist, the search is limited to the corresponding part of the table.