15.10 - Predicate Marshaling - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

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. Other query rewrites are also sometimes undertaken by the Optimizer rather than 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” on page 305 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.

Conditions on multiple relations are converted by expansion. For example,

     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.

Where possible, the Optimizer derives new conditions using transitive closure. See “Predicate Simplification” on page 94 for additional information about transitive closure.

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.

The next step is to push the marshaled predicates down the parse tree as far as possible. See “Translation to Internal Representation” on page 129 and “Predicate Pushdown and Pullup” on page 104 for additional information on predicate push down and pullup.