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
- 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.