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 Filter in the access plan. For example, various algorithms, such as IndexScan, have the ability to enforce the application of a predicate without the need for an explicit operator. For example, consider a BETWEEN predicate involving two literal constants, and the column referenced in the predicate is indexed. 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 a Filter. Predicates that are join conditions also do not normally appear in an access plan as a filter.

PreFilter is the same as Filter, except that the expressions used in the predicates of a PreFilter 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.

See also

• The WHERE clause: Specifying rows
• EXISTS search condition