Overview of Query Rewriute

When base tables contain large amount of data, it is an expensive and time consuming
process to compute the required aggregates or to compute joins between these tables.
In such cases, queries can take minutes or even hours to return the answer. Because
materialized views contain already precomputed aggregates and joins, Oracle
employs an extremely powerful process called query rewrite to quickly answer the
query using materialized views.

One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. The transformation is transparent to the end user or application, requiring no intervention and no reference to the materialized view in the SQL statement. Because query rewrite is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.

A query undergoes several checks to determine whether it is a candidate for query
rewrite. If the query fails any of the checks, then the query is applied to the detail tables rather than the materialized view. This can be costly in terms of response time and processing power.

The optimizer uses two different methods to recognize when to rewrite a query in
terms of a materialized view. The first method is based on matching the SQL text of
the query with the SQL text of the materialized view definition. If the first method
fails, the optimizer uses the more general method in which it compares joins,
selections, data columns, grouping columns, and aggregate functions between the
query and materialized views.

Query rewrite operates on queries and subqueries in the following types of SQL
statements:

■ SELECT
■ CREATE TABLE … AS SELECT
■ INSERT INTO … SELECT


It also operates on subqueries in the set operators UNION, UNION ALL, INTERSECT,
and MINUS, and subqueries in DML statements such as INSERT, DELETE, and
UPDATE.

Several factors affect whether or not a given query is rewritten to use one or more
materialized views:

■ Enabling or disabling query rewrite
– By the CREATE or ALTER statement for individual materialized views
– By the session parameter QUERY_REWRITE_ENABLED
– By the REWRITE and NOREWRITE hints in SQL statements
■ Rewrite integrity levels
■ Dimensions and constraints

The DBMS_MVIEW.EXPLAIN_REWRITE procedure advises whether query rewrite is
possible on a query and, if so, which materialized views will be used. It also explains why a query cannot be rewritten.

When Does Oracle Rewrite a Query?
A query is rewritten only when a certain number of conditions are met:
■ Query rewrite must be enabled for the session.
■ A materialized view must be enabled for query rewrite.
■ The rewrite integrity level should allow the use of the materialized view. For
example, if a materialized view is not fresh and query rewrite integrity is set to
ENFORCED, then the materialized view is not used.
■ Either all or part of the results requested by the query must be obtainable from the precomputed result stored in the materialized view or views.

To determine this, the optimizer may depend on some of the data relationships
declared by the user using constraints and dimensions. Such data relationships
include hierarchies, referential integrity, and uniqueness of key data, and so on.

Posted in: Query Rewrite