|
Filters apply search conditions including any type of predicate, comparisons involving subselects, and EXISTS and NOT EXISTS subqueries (and other forms of quantified subqueries). The search conditions appear in the statement in the WHERE and HAVING clauses, and in the ON conditions of JOINS in the FROM clause.
The optimizer is free to simplify and alter the set of predicates in the search condition as it sees fit, and to construct an access plan that applies the conditions in an order different from the order specified in the original statement. Query rewrite optimizations may make substantial changes to the set of predicates evaluated in a plan.
In a number of situations, a predicate in a query may not result in the existence of a Filter algorithm in the access plan. For example, various algorithms, such as an Index Scan, have the ability to enforce the application of a predicate without the need for an explicit operator. As a concrete example, consider a BETWEEN predicate involving two literal constants, and the column referenced in predicate is indexed. In this case, the BETWEEN predicate can be enforced by the lower and upper bounds of the index scan, and the plan for the query will not contain an explicit Filter algorithm. Predicates that are join conditions also do not normally appear in an access plan as a filter.
A Pre-filter algorithm is the same as a Filter algorithm, except that the expressions used in the predicates of a Pre-filter do not depend on any table or view referenced in the query. As a simple example, the search condition in the clause WHERE 1 = 2 can be evaluated as a pre-filter.