Query Rewrite | SQL Request and Transaction Processing | Teradata Vantage - 17.10 - Query Rewrite - 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)

About Query Rewrite

For the Teradata system, query rewrites are mostly performed in the Query Rewrite Subsystem of the Parser; however, some query rewrites are performed in the later phases. The Query Rewrite Subsystem is invoked by the Parser just prior to query optimization, and is the first phase of optimization performed in the query optimization process.

Function of Query Rewrite

Stated semi-formally, Query Rewrite is the process of rewriting query Q as query Q’ such that the following criteria are both true:
  • Query Q and Query Q’ produce the identical answer set.
  • Query Q’ runs faster (which means that it is less costly) than Query Q.

The various Query Rewrite techniques can either be rule-based (such as predicate move around) or cost-based (such as join index substitution)

With many SQL queries now being created by query generator tools that do not write optimally efficient code for a given target SQL processor, Query Rewrite has become more crucial than ever. Even trivial SQL requests can be written in an enormously large number of different ways.

Consider the following verbally expressed query: “Get the names of suppliers who supply part P2.” Using the features available in the ANSI SQL-92 version of the language, it is possible to express this query in at least 52 different ways, assuming the system must access two tables in the process of returning the answer set. This example only indicates the number of possible ways of writing the verbally expressed query as an SQL request; it does not include the number of internal rewrites of those 52 individual SQL queries.