Push-Down Filters
Push-down filters are strictly an internal optimization technique. By taking advantage of high speed filtering capabilities, the database engine can efficiently identify certain rows to be rejected from the result set depending on characteristics of the restriction. Because rows are rejected from the result set before they are returned, the database engine has to analyze fewer rows and completes the operation faster than it would without push-down filters.
The database engine can use an expression or combination of expressions as a push-down filter if the following conditions are satisfied:
•
A Predicate can be used in a push-down filter if it is joined to the rest of the Restriction by the AND operator.
•
A Predicate can be used in a push-down filter if one operand consists of a column reference and the other operand consists of either a literal value or a dynamic parameter (“?”). Also, the referenced column must not be of one of the following data types: bit, float, double, real, longvarchar, longvarbinary, or binary.
•
A Predicate can be used in a push-down filter if the comparison operator is one of the following: <, <=, =, >=, >, or <>.
•
A Disjunct can be used in a push-down filter if it is joined to the rest of the restriction by the AND operator and all the predicates within the disjunct satisfy the requirements for a predicate to be used in a push-down filter, except for the condition that the predicate must be joined to the rest of the restriction by an AND operator. Only one disjunct may be included in the push-down filter.
•
A push-down filter may combine a single disjunct with other predicates that satisfy the requirements for a predicate to be used in a push-down filter.
For definitions of the technical terms used in this chapter, see Terminology.