The first step in the process of query rewrites undertaken by the Optimizer is to marshal the predicates, both ordinary and connecting, for the DML operations presented to the Optimizer by the rewritten ResTree´. This application of transitive closure is not undertaken by the Query Rewrite subsystem.
In the process of marshaling predicates, the query predicates are first, whenever possible, isolated from the parse tree, converted to conjunctive normal form, or CNF (defined in Path Selection), and possibly combined with other predicates or eliminated from the query altogether by a process analogous to factoring and cancellation in ordinary algebra.
Applying Predicate Conditions
Conditions on multiple relations are converted by expansion. For example, consider the following:
A + CD --> (A+C) (A+D) AB + CD --> (A+C) (A+D) (B+C) (B+D)
where the symbol --> indicates transformation.
Conditions on a single relation are needed to generate efficient access paths for that relation, and are therefore not converted. Consider the following single-relation condition set.
(NUSI_1 = 1 AND NUSI_2 = 2) OR (NUSI_1 = 3 AND NUSI_2 = 4)
Each ORed expression can be used to read the NUSI subtable to generate a RowID spool. The maximum number of ORed expressions that can be specified is 1,048,546.
Transitive Closure
Where possible, the Optimizer derives new conditions using transitive closure. See Predicate Simplification for additional information about transitive closure.
Connecting Predicates
A connecting predicate is one that connects an outer query with a subquery. For example, consider the following transformation:
(table_1.x, table_1.y) IN (SELECT table_2.a, table_2.b FROM table_2) --> (table_1.x IN spool_1.a) AND (table_1.y IN spool_1.b)
Similarly, the following transformation pushes a constant to spool.
(table_1.x, constant) IN (SELECT table_2.a, table_2.b FROM table_2) --> (table_1.x IN spool_1.a)
The term (table_2.b = constant) is pushed to spool_1.
The following transformation is more complex.
(table_1.x, table_1.y) IN (SELECT table_2.a, constant FROM table_2) --> (table_1.x IN spool_1.a) AND (table_1.y = spool_1.constant)
The more connecting conditions available, the more flexible the plan.
Pushing Predicates down the Parse Tree
The next step is to push the marshaled predicates down the parse tree as far as possible. See Translation to Internal Representation and Predicate Pushdown and Pullup for additional information on predicate push down and pullup.