SQL Engine - Query Rewrite
1 - About
It is aimed to rewrite the query against a materialized view (a view stored in a table fashion) which contains by nature less records and then improve the performance of the query.
The real power behind aggregate table and query rewrite is that their use is transparent to the user. In the same way that a user doesn’t have to know about the indexes on a table to use them, then likewise, a user doesn’t have to know about the presence, structure and content of the materialized view. Query Rewrite enables this transparent use of aggregate table and is a query optimisation mechanism whereby the original query SQL, which is written against the base tables, is automatically rewritten by the optimiser to access the appropriate materialized views.
A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
The query rewrite function uses:
- Hierarchies (They represent a 1:n relationship inside one table. For instance in Oracle with a materialized view query rewrite using join back and roll up
2 - Articles Related
3 - More
Normally, when QUERY REWRITE ENABLED is set to FALSE, the database will take your SQL as is, parse it, and optimize it. With query rewrites enabled, The database will insert an extra step into this process. The query transformer do the job. After parsing, it will attempt to rewrite the query to access some materialized view, instead of the actual table that it references. If it can perform a query rewrite, the rewritten query (or queries) is parsed and then optimized along with the original query. The query plan with the lowest cost from this set is chosen for execution. If it cannot rewrite the query, the original parsed query is optimized and executed as normal.