15.10 - Typical Query Rewrite Techniques - Teradata Database

Because relational systems are based on rigorous mathematical principles, there is a good deal of algebraic simplification and term reduction that can be made for most queries. Similarly, there are often semantically equivalent operations that can be substituted for analytically more difficult operations in order to simplify processing. The vast majority of these semantically equivalent rewrites cannot be specified in the query itself, examples being the substitution of a join index for an explicitly specified join operation and the pushing of projections (see, for example, McMahan et al. (2004) and, for details, “Predicate Pushdown and Pullup” on page 104).

The following sequence lists many of the more important methods used by the Query Rewrite subsystem to rewrite SQL code:

View folding (see “View Folding” on page 88)

ANSI join syntax‑to‑comma join syntax (see “Converting ANSI Join Syntax To Comma Join Syntax” on page 83)

Predicate marshaling (see “Predicate Marshaling” on page 114)

Predicate marshaling is also referred to as predicate move around.

Predicate push down and pullup (see “Predicate Pushdown and Pullup” on page 104)

Predicate push down and pullup is also referred to as pushing conditions.

Outer join‑to‑inner join conversion (see “Outer Join‑to‑Inner Join Conversion” on page 86)

Predicate simplification, including satisfiability and transitive closure (see “Predicate Simplification” on page 94)

Join elimination (see “Eliminating Redundant Joins” on page 107)

Set operation branch elimination (see “Eliminating Set Operation Branches” on page 106)

Pushing joins into UNION ALL branches (see “Pushing Joins Into UNION ALL Branches” on page 112)

View materialization and other database object substitutions (see “View Materialization and Other Database Object Substitutions” on page 116)