SQL Engine - Query Rewrite

Oracle Database Sql Processing


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:


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.


Discover More
Data System Architecture
Data Warehouse

A data warehouse is a large central data repository of current, history and summarised data coming from operational and external sources used primarily for analysis. s is large historical databases for...
Star Schema
Dimensional Data Modeling - Dimension (Perspective)

A dimension is a part of a dimensional schema and provide the basis for analyzing data through slicing and dicing A dimension is just a set of descriptif attribute that are generally grouped: in a...
Star Schema
Dimensional Data Modeling - Hierarchy

in dimensional data modeling A hierarchy is a set of levels having many-to-one relationships between each other, and the set of levels collectively makes up a dimension in the form of a tree (A tree...
Obiee Aggregate Lts
OBIEE - Aggregate Navigation with level-based fragmentation

OBI Server features have the ability to retrieve queries from an aggregate table defined through level of the hierarchy instead of the fact table. This ability is a part of the aggregate navigation feature....
Data System Architecture
Relational Data Modeling - (Integrity) (Constraints|action assertions)

Constraints are a set of rule inside a relational database that declare consistency rules in order to: enforce data integrity and give information on the data (used by the query optimizer) Every...
Data System Architecture
Relational Data Modeling - Aggregate Table (Summary Table)

An Aggregate table is a persistence unit of aggregate data. Aggregate tables are also known as summary table (from summary) materialized view A aggregate is a table that contains the result of a query...
Data System Architecture
Relational Data Modeling - View selection problem (recommending the best aggregation tables) - Data Warehousing

The View Selection Problem (VSP) is an NP-Complete problem. Challenges: Design Which materializations to create? Populate Load them with data Maintain Incrementally populate when data changes...
SQL - Materialized View

A materialized view is an auxiliary data structure that persist the result of a query in a table format managed by the cache system of the database. The refresh of the data is defined in criteria: ...
Data System Architecture
SQL - Query Performance

The query performance is the of a query. OLTP query are query that comes from an OLTP application. This kind of query retrieve few rows and their performance is generally improved with a index....
Query Optimizer Process
SQL Engine - The Query Transformer

The query transformer is a component of the query optimizer and take as input a parsed query, which is represented by a set of query blocks. The query blocks are nested or interrelated to each other....

Share this page:
Follow us:
Task Runner