Restriction Analysis
This section explains one method that the database engine uses to analyze and optimize on a Restriction. For definitions of the technical terms used in this chapter, see Terminology.
Modified CNF Conversion
During SQL statement execution, the database engine attempts to convert the restriction into Modified Conjunctive Normal Form (Modified CNF). Conversion to modified CNF is a method for placing Boolean expressions in a uniform structure to facilitate restriction analysis for possible query processing optimizations. If the restriction can be converted to modified CNF, the database engine can methodically and thoroughly analyze the query for possible optimizations that make efficient use of available Indexes. If the database engine is unable to convert the restriction to modified CNF, it still analyzes the restriction for possible optimizations. In this case, however, the database engine is often unable to make use of the available indexes as effectively as it would for restrictions that either are already in modified CNF or can be converted internally to modified CNF.
Restrictions that Cannot be Converted
The database engine is unable to convert some restrictions into modified CNF depending on the contents of the restriction. A restriction is not converted to modified CNF if any of the following conditions is true:
•
The restriction contains a subquery.
•
The restriction contains a NOT operator.
•
The restriction contains a dynamic parameter (a dynamic parameter is a question mark (“?”) in the SQL statement, which will be prompted for when the statement is executed).
Conditions Under Which Conversion is Avoided
There are some cases in which the database engine may be capable of converting a restriction into modified CNF but will not do so. The database engine chooses not to convert a restriction to modified CNF in cases where it has determined that the restriction is more likely to benefit from optimizations that can be applied to its original form than from optimizations that could be applied after modified CNF conversion.
A restriction is not converted to modified CNF if either of the following conditions is true:
•
The restriction is in Disjunctive Normal Form (DNF) and all Predicates involve only the equal (=), LIKE or IN comparison operators.
For example, the database engine does not convert the following restriction to modified CNF:
(c1 = 1 AND c2 = 1) OR (c1 = 1 AND c2 = 2) OR (c1 = 2)
•
The restriction meets all of the following conditions:
•
It contains an expression in Disjunctive Normal Form (DNF) that is AND connected to the rest of the restriction
•
The specified DNF expression contains only Predicates that involve the equal (=), LIKE or IN comparison operator
•
The predicates in identical positions in each Conjunct in the DNF expression reference the same column.
For example, a Restriction that contains the following Expression will not be converted to modified CNF:
(c1 = 1 AND c2 = 1) OR (c1 = 1 AND c2 = 2)