- Both queries produce the identical result with exceptions that the result order may differ if there is not an ORDER BY clause for the query and a rewrite may expose or hide an error in the query. For example, a divide by zero error may be avoided when the error would have occurred if a join was not eliminated. Also, a divide by zero error may occur if predicates are reordered based on commutative and associative rules of predicate evaluation.
Q' runs faster than Q.
In many cases, multiple query rewrites can be applied to a query. Also, a query rewrite or feedback from query results can trigger opportunities for further query rewrites. Query rewrites can be applied both in the Query Rewrite subsystem prior to query optimization, and also during query optimization.
The following are some of the query rewrites that may be applied:
- Substituting underlying base tables and covering indexes for views and derived tables (a method called view folding)
- Substituting hash and join indexes for underlying base tables, views, or join operations
- Converting outer joins to inner joins
- Converting INNER join syntax to the equivalent comma syntax
- Eliminating unnecessary joins
- Using logical satisfiability and transitive closure either to eliminate terms or to add terms that facilitate further rewrites
- Simplifying predicates
- Pushing projections and predicate conditions into spooled views
- Eliminating set operation branches
- Pushing aggregations and joins into UNION ALL branches
A query rewrite can be rule-based, such as predicate pushdown, or cost-based, such as rewriting a query to use a join index.