For the Teradata system, query rewrites are typically performed in the Query Rewrite Subsystem of the Parser. However, query rewrites may be performed in the later phases. The Query Rewrite Subsystem is invoked by the Parser immediately before query optimization, and is the first phase of optimization performed in the query optimization process.
Function of Query Rewrite
- Query Q and Query Q’ produce the identical answer set.
- Query Q’ runs faster (and is therefore less costly) than Query Q.
The Query Rewrite techniques can either be rule-based (such as predicate move around) or cost-based (such as join index substitution)
Because query generator tools do not write optimally efficient code for a given target SQL processor, Query Rewrite is crucial.
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, you can express this query in at least 52 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, and does not include the number of internal rewrites of those 52 individual SQL queries.