About

query rewrite is a query performance technique enabled by the query transformer that rewrites the original query to use materialized view instead.

As the materialized view (a view stored in a table fashion) contains by nature less records, the performance of the query is then improved.

The real power behind materialized view 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:

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.

Rewrite