17.10 - View Materialization and Other Database Object Substitutions - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Perhaps the most obvious query rewrite concern is instantiating virtual database objects and replacing specified query structures with more high-performing substitutes when possible.

For example, all views referenced by a query must be resolved into their underlying base tables or covering indexes before the query can be performed. These rewrite methods also replace base tables and table joins with hash, join, or even secondary indexes whenever the substitution makes the query perform more optimally. These substitutions also apply to view materialization if base tables referenced by the view can be replaced profitably, Partial GROUP BY rewrites, and Common Spool Usage rewrites. The term materialized view is sometimes used to describe database objects like snapshot summary tables, and hash or join indexes. For purposes of the current discussion, the term refers to materializing the base table components of a view definition in a spool relation.

For various reasons, these query rewrites are performed by the Optimizer subsystem after the Query Rewrite subsystem has completed its various rewrite tasks.