During the query analysis, the optimizer examines the query for search arguments, the use of the OR operator, and the existence of join criteria, in that order. Because the use of the OR operator and the existence of join criteria are self-explanatory, only search arguments are discussed.
A search argument is the part of a query that restricts the intermediate result set of the query. The main purpose of search arguments is to allow the use of existing indices in relation to the given expression. The following are examples of search arguments:
- emp_fname = ‘Moser’
- salary >= 50000
- emp_fname = ‘Moser’ AND salary >= 50000
There are several expression forms that cannot be used by the optimizer as search arguments. To the first group belongs all expressions with the NOT (<>) operator. Also, if you use the expression on the left side of the operator, the existing expression cannot be used as a search argument.
The following are examples of expressions that are not search arguments:
- NOT IN (‘d1’, ‘d2’)
- emp_no <> 9031
- budget * 0.59 > 55000
The main disadvantage of expressions that cannot be used as search arguments is that the optimizer cannot use existing indices in relation to the expression to speed up the performance of the corresponding query. In other words, the only access the optimizer uses in this case is the table scan.
[…] the given query. To find the most efficient execution plan, the query optimizer first makes the query analysis, during which it searches for search arguments and join operations. The optimizer then selects […]