Predicate Marshaling - Analytics Database - Teradata Vantage

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´. Note that this particular 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 (see Path Selection for a definition of CNF), and then they might be 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 not converted because they are needed to generate efficient access paths for that relation. Consider the following single-relation condition set.

     (NUSI_1 = 1 AND NUSI_2 = 2) OR (NUSI_1 = 3 AND NUSI_2 = 4)

In this case, 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 deals with a constant by pushing it 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.