Types of Query Rewrite

Queries that have aggregates that require computations over a large number of rows
or joins between very large tables can be expensive and thus can take a long time to
return the results. Query rewrite transparently rewrites such queries using
materialized views that have pre-computed results, so that the queries can be
answered almost instantaneously. These materialized views can be broadly
categorized into two groups, namely materialized aggregate views and materialized
join views.

Materialized aggregate views are tables that have pre-computed aggregate
values for columns from original tables. Similarly, materialized join views are tables that have pre-computed joins between columns from original tables. Query rewrite transforms an incoming query to fetch the results from materialized view columns.

Since these columns contain already pre-computed results, the incoming query can be
answered almost instantaneously.

This section discusses the following methods that can be used to rewrite a query:

■ Text Match Rewrite
■ Join Back
■ Aggregate Computability
■ Aggregate Rollup
■ Rollup Using a Dimension
■ When Materialized Views Have Only a Subset of Data
■ Partition Change Tracking (PCT) Rewrite
■ Multiple Materialized Views



Text Match Rewrite
The query rewrite engine always initially tries to compare the text of incoming query
with the text of the definition of any potential materialized views to rewrite the query.
This is because the overhead of doing a simple text comparison is usually negligible
comparing to the cost of doing a complex analysis required for the general rewrite.
The query rewrite engine uses two text match methods, full text match rewrite and
partial text match rewrite. In full text match the entire text of a query is compared
against the entire text of a materialized view definition (that is, the entire SELECT
expression), ignoring the white space during text comparison. For example, assume
that we have the following materialized view, sum_sales_pscat_month_city_mv:

CREATE MATERIALIZED VIEW sum_sales_pscat_month_city_mv
ENABLE QUERY REWRITE AS
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold,
COUNT(s.amount_sold) AS count_amount_sold
FROM sales s, products p, times t, customers c
WHERE s.time_id=t.time_id
AND s.prod_id=p.prod_id
AND s.cust_id=c.cust_id
GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;

Consider the following query:

SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold,
COUNT(s.amount_sold) AS count_amount_sold
FROM sales s, products p, times t, customers c
WHERE s.time_id=t.time_id
AND s.prod_id=p.prod_id
AND s.cust_id=c.cust_id
GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;

This query matches sum_sales_pscat_month_city_mv (white space excluded)
and is rewritten as:

SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city,
mv.sum_amount_sold, mv.count_amount_sold
FROM sum_sales_pscat_month_city_mv;

When full text match fails, the optimizer then attempts a partial text match. In this
method, the text starting from the FROM clause of a query is compared against the text starting with the FROM clause of a materialized view definition. Therefore, the
following query can be rewritten:

SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,
AVG(s.amount_sold)
FROM sales s, products p, times t, customers c
WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id
AND s.cust_id=c.cust_id
GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;

This query is rewritten as:

SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city,
mv.sum_amount_sold/mv.count_amount_sold
FROM sum_sales_pscat_month_city_mv mv;

Note that, under the partial text match rewrite method, the average of sales aggregate required by the query is computed using the sum of sales and count of sales
aggregates stored in the materialized view.

When neither text match succeeds, the optimizer uses a general query rewrite method.
Text match rewrite can distinguish contexts where the difference between uppercase
and lowercase is significant and where it is not. For example, the following statements are equivalent:

SELECT X, 'aBc' FROM Y
Select x, 'aBc' From y

Join Back

If some column data requested by a query cannot be obtained from a materialized
view, the optimizer further determines if it can be obtained based on a data
relationship called a functional dependency. When the data in a column can determine
data in another column, such a relationship is called a functional dependency or
functional determinance. For example, if a table contains a primary key column called
prod_id and another column called prod_name, then, given a prod_id value, it is
possible to look up the corresponding prod_name. The opposite is not true, which
means a prod_name value need not relate to a unique prod_id.

When the column data required by a query is not available from a materialized view,
such column data can still be obtained by joining the materialized view back to the
table that contains required column data provided the materialized view contains a
key that functionally determines the required column data. For example, consider the
following query:

SELECT p.prod_category, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND p.prod_category='CD'
GROUP BY p.prod_category, t.week_ending_day;

The materialized view sum_sales_prod_week_mv contains p.prod_id, but not
p.prod_category. However, you can join sum_sales_prod_week_mv back to
products to retrieve prod_category because prod_id functionally determines
prod_category. The optimizer rewrites this query using sum_sales_prod_week_
mv as follows:

SELECT p.prod_category, mv.week_ending_day, SUM(mv.sum_amount_sold)
FROM sum_sales_prod_week_mv mv, products p
WHERE mv.prod_id=p.prod_id AND p.prod_category='CD'
GROUP BY p.prod_category, mv.week_ending_day;

Here the products table is called a joinback table because it was originally joined in the materialized view but joined again in the rewritten query.

You can declare functional dependency in two ways:

■ Using the primary key constraint (as shown in the previous example)
■ Using the DETERMINES clause of a dimension

The DETERMINES clause of a dimension definition might be the only way you could
declare functional dependency when the column that determines another column
cannot be a primary key. For example, the products table is a denormalized
dimension table that has columns prod_id, prod_name, and prod_subcategory
that functionally determines prod_subcat_desc and prod_category that
determines prod_cat_desc.

The first functional dependency can be established by declaring prod_id as the
primary key, but not the second functional dependency because the prod_
subcategory column contains duplicate values. In this situation, you can use the
DETERMINES clause of a dimension to declare the second functional dependency.

The following dimension definition illustrates how functional dependencies are
declared:

CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory)
LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF
category
)
ATTRIBUTE product DETERMINES products.prod_name

ATTRIBUTE product DETERMINES products.prod_desc
ATTRIBUTE subcategory DETERMINES products.prod_subcat_desc
ATTRIBUTE category DETERMINES products.prod_cat_desc;

The hierarchy prod_rollup declares hierarchical relationships that are also 1:n
functional dependencies. The 1:1 functional dependencies are declared using the
DETERMINES clause, as seen when prod_subcategory functionally determines
prod_subcat_desc.

If the following materialized view is created:

CREATE MATERIALIZED VIEW sum_sales_pscat_week_mv
ENABLE QUERY REWRITE AS
SELECT p.prod_subcategory, t.week_ending_day,
SUM(s.amount_sold) AS sum_amount_sole
FROM sales s, products p, times t
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id
GROUP BY p.prod_subcategory, t.week_ending_day;

Then consider the following query:

SELECT p.prod_subcategory_desc, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id
AND p.prod_subcat_desc LIKE '%Men'
GROUP BY p.prod_subcat_desc, t.week_ending_day;

This can be rewritten by joining sum_sales_pscat_week_mv to the products table
so that prod_subcat_desc is available to evaluate the predicate. However, the join
will be based on the prod_subcategory column, which is not a primary key in the
products table; therefore, it allows duplicates. This is accomplished by using an
inline view that selects distinct values and this view is joined to the materialized view as shown in the rewritten query.

SELECT iv.prod_subcat_desc, mv.week_ending_day, SUM(mv.sum_amount_sold)
FROM sum_sales_pscat_week_mv mv,
(SELECT DISTINCT prod_subcategory, prod_subcat_desc
FROM products) iv
WHERE mv.prod_subcategory=iv.prod_subcategory
AND iv.prod_subcat_desc LIKE '%Men'
GROUP BY iv.prod_subcat_desc, mv.week_ending_day;

This type of rewrite is possible because prod_subcategory functionally determines
prod_subcategory_desc as declared in the dimension.


Aggregate Computability
Query rewrite can also occur when the optimizer determines if the aggregates
requested by a query can be derived or computed from one or more aggregates stored
in a materialized view. For example, if a query requests AVG(X) and a materialized
view contains SUM(X) and COUNT(X), then AVG(X) can be computed as
SUM(X)/COUNT(X).
In addition, if it is determined that the rollup of aggregates stored in a materialized view is required, then, if it is possible, query rewrite also rolls up each aggregate requested by the query using aggregates in the materialized view.
For example, SUM(sales) at the city level can be rolled up to SUM(sales) at the
state level by summing all SUM(sales) aggregates in a group with the same state
value. However, AVG(sales) cannot be rolled up to a coarser level unless COUNT(sales) or SUM(sales) is also available in the materialized view. Similarly,
VARIANCE(sales) or STDDEV(sales) cannot be rolled up unless both
COUNT(sales) and SUM(sales) are also available in the materialized view. For
example, consider the following query:

ALTER TABLE times MODIFY CONSTRAINT time_pk RELY;
ALTER TABLE customers MODIFY CONSTRAINT customers_pk RELY;
ALTER TABLE sales MODIFY CONSTRAINT sales_time_pk RELY;
ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk RELY;
SELECT p.prod_subcategory, AVG(s.amount_sold) AS avg_sales
FROM sales s, products p WHERE s.prod_id = p.prod_id


This statement can be rewritten with materialized view sum_sales_pscat_month_
city_mv provided the join between sales and times and sales and customers
are lossless and non-duplicating. Further, the query groups by prod_subcategory
whereas the materialized view groups by prod_subcategory, calendar_month_
desc and cust_city, which means the aggregates stored in the materialized view
will have to be rolled up. The optimizer rewrites the query as the following:

SELECT mv.prod_subcategory, SUM(mv.sum_amount_sold)/COUNT(mv.count_amount_sold)
AS avg_sales
FROM sum_sales_pscat_month_city_mv mv
GROUP BY mv.prod_subcategory;

The argument of an aggregate such as SUM can be an arithmetic expression such as
A+B. The optimizer tries to match an aggregate SUM(A+B) in a query with an
aggregate SUM(A+B) or SUM(B+A) stored in a materialized view. In other words,
expression equivalence is used when matching the argument of an aggregate in a
query with the argument of a similar aggregate in a materialized view. To accomplish
this, Oracle converts the aggregate argument expression into a canonical form such
that two different but equivalent expressions convert into the same canonical form.
For example, A*(B-C), A*B-C*A, (B-C)*A, and -A*C+A*B all convert into the same
canonical form and, therefore, they are successfully matched.


Aggregate Rollup
If the grouping of data requested by a query is at a coarser level than the grouping of data stored in a materialized view, the optimizer can still use the materialized view to rewrite the query. For example, the materialized view sum_sales_pscat_week_mv
groups by prod_subcategory and week_ending_day. This query groups by
prod_subcategory, a coarser grouping granularity:

ALTER TABLE times MODIFY CONSTRAINT time_pk RELY;
ALTER TABLE sales MODIFY CONSTRAINT sales_time_fk RELY;
SELECT p.prod_subcategory, SUM(s.amount_sold) AS sum_amount
FROM sales s, products p
WHERE s.prod_id=p.prod_id
GROUP BY p.prod_subcategory;

Therefore, the optimizer will rewrite this query as:

SELECT mv.prod_subcategory, SUM(mv.sum_amount_sold)
FROM sum_sales_pscat_week_mv mv
GROUP BY mv.prod_subcategory;

Read More

Checks Made by Query Rewrite

For query rewrite to occur, there are a number of checks that the data must pass. These
checks are:

■ Join Compatibility Check
■ Data Sufficiency Check
■ Grouping Compatibility Check
■ Aggregate Computability Check

Join Compatibility Check
In this check, the joins in a query are compared against the joins in a materialized
view. In general, this comparison results in the classification of joins into three
categories:

■ Common joins that occur in both the query and the materialized view. These joins
form the common subgraph.
■ Delta joins that occur in the query but not in the materialized view. These joins
form the query delta subgraph.
■ Delta joins that occur in the materialized view but not in the query. These joins
form the materialized view delta subgraph.


Data Sufficiency Check
In this check, the optimizer determines if the necessary column data requested by a
query can be obtained from a materialized view. For this, the equivalence of one
column with another is used. For example, if an inner join between table A and table B is based on a join predicate A.X = B.X, then the data in column A.X will equal the
data in column B.X in the result of the join. This data property is used to match
column A.X in a query with column B.X in a materialized view or vice versa. For
example, consider the following query:

SELECT p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;

This query can be answered with join_sales_time_product_mv even though the
materialized view does not have s.time_id. Instead, it has t.time_id, which,
through a join condition s.time_id=t.time_id, is equivalent to s.time_id. Thus,
the optimizer might select the following rewrite:

SELECT prod_name, time_id, week_ending_day, SUM(amount_sold)
FROM join_sales_time_product_mv
GROUP BY prod_name, time_id, week_ending_day;


Grouping Compatibility Check
This check is required only if both the materialized view and the query contain a
GROUP BY clause. The optimizer first determines if the grouping of data requested by a query is exactly the same as the grouping of data stored in a materialized view. In
other words, the level of grouping is the same in both the query and the materialized
view. If the materialized views groups on all the columns and expressions in the query and also groups on additional columns or expressions, query rewrite can reaggregate the materialized view over the grouping columns and expressions of the query to derive the same result requested by the query.


Aggregate Computability Check
This check is required only if both the query and the materialized view contain
aggregates. Here the optimizer determines if the aggregates requested by a query can
be derived or computed from one or more aggregates stored in a materialized view.
For example, if a query requests AVG(X) and a materialized view contains SUM(X)
and COUNT(X), then AVG(X) can be computed as SUM(X)/COUNT(X).
If the grouping compatibility check determined that the rollup of aggregates stored in a materialized view is required, then the aggregate computability check determines if it is possible to roll up each aggregate requested by the query using aggregates in the materialized view.

Read More

How Oracle Rewrites Queries

The optimizer uses a number of different methods to rewrite a query. The first step in determining whether query rewrite is possible is to see if the query satisfies the
following prerequisites:

■ Joins present in the materialized view are present in the SQL.
■ There is sufficient data in the materialized view(s) to answer the query.

After that, it must determine how it will rewrite the query. The simplest case occurs
when the result stored in a materialized view exactly matches what is requested by a
query. The optimizer makes this type of determination by comparing the text of the
query with the text of the materialized view definition. This text match method is most straightforward but the number of queries eligible for this type of query rewrite is minimal.

When the text comparison test fails, the optimizer performs a series of generalized
checks based on the joins, selections, grouping, aggregates, and column data fetched.
This is accomplished by individually comparing various clauses (SELECT, FROM,
WHERE, HAVING, or GROUP BY) of a query with those of a materialized view.
This section discusses the optimizer in more detail, as well as the following types of query rewrite:

■ Text Match Rewrite
■ General Query Rewrite Methods

Read More

Example of Query Rewrite

Consider the following materialized view, cal_month_sales_mv, which provides
an aggregation of the dollar amount sold in every month:

CREATE MATERIALIZED VIEW cal_month_sales_mv
ENABLE QUERY REWRUTE AS
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_mont_desc;

Let us say that, in a typical month, the number of sales in the store is around one
million. So this materialized aggregate view will have the precomputed aggregates for
the dollar amount sold for each month. Now consider the following query, which asks
for the sum of the amount sold at the store for each calendar month:

SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

In the absence of the previous materialized view and query rewrite feature, Oracle will have to access the sales table directly and compute the sum of the amount sold to return the results. This involves reading many million rows from the sales table
which will invariably increase the query response time due to the disk access. The join in the query will also further slow down the query response as the join needs to be computed on many million rows. In the presence of the materialized view cal_
month_sales_mv, query rewrite will transparently rewrite the previous query into
the following query:

SELECT calendar_month, dollars
FROM cal_month_sales_mv;

Because there are only a few dozens rows in the materialized view cal_month_
sales_mv and no joins, Oracle will return the results instantly. This simple example
illustrates the power of query rewrite with materialized views!

Read More

Ensuring that Query Rewrite takes Effect

You must follow several steps to enable query rewrite:

1. Individual materialized views must have the ENABLE QUERY REWRITE clause.
2. The session parameter QUERY_REWRITE_ENABLED must be set to TRUE (the
default) or FORCE.
3. Cost-based optimization must be used by setting the initialization parameter
OPTIMIZER_MODE to ALL_ROWS, FIRST_ROWS, or FIRST_ROWS_n.

If step 1 has not been completed, a materialized view will never be eligible for query rewrite. You can specify ENABLE QUERY REWRITE either with the ALTER
MATERIALIZED VIEW statement or when the materialized view is created, as
illustrated in the following:

CREATE MATERIALIZED VIEW join_sales_time_product_mv
ENABLE QUERY REWRITE AS
SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day,
s.channel_id, s.promo_id, s.cust_id, s.amount_sold
FROM sales s, products p, times t
WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id;

The NOREWRITE hint disables query rewrite in a SQL statement, overriding the
QUERY_REWRITE_ENABLED parameter, and the REWRITE hint (when used with mv_
name) restricts the eligible materialized views to those named in the hint.

You can use the DBMS_ADVISOR.TUNE_MVIEW to optimize a CREATE MATERIALIZED
VIEW statement to enable general QUERY REWRITE.


Initialization Parameters for Query Rewrite
The following three initialization parameter settings control query rewrite behavior:

■ OPTIMIZER_MODE = ALL_ROWS (default), FIRST_ROWS, or FIRST_ROWS_n
With OPTIMIZER_MODE set to FIRST_ROWS, the optimizer uses a mix of costs and
heuristics to find a best plan for fast delivery of the first few rows. When set to
FIRST_ROWS_n, the optimizer uses a cost-based approach and optimizes with a
goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).

■ QUERY_REWRITE_ENABLED = TRUE (default), FALSE, or FORCE
This option enables the query rewrite feature of the optimizer, enabling the
optimizer to utilize materialized view to enhance performance. If set to FALSE,
this option disables the query rewrite feature of the optimizer and directs the
optimizer not to rewrite queries using materialized views even when the
estimated query cost of the unrewritten query is lower.
If set to FORCE, this option enables the query rewrite feature of the optimizer and
directs the optimizer to rewrite queries using materialized views even when the
estimated query cost of the unwritten query is lower.

■ QUERY_REWRITE_INTEGRITY
This parameter is optional, but must be set to STALE_TOLERATED, TRUSTED, or
ENFORCED
By default, the integrity level is set to ENFORCED. In this mode, all constraints
must be validated. Therefore, if you use ENABLE NOVALIDATE RELY, certain types
of query rewrite might not work. To enable query rewrite in this environment
(where constraints have not been validated), you should set the integrity level to a
lower level of granularity such as TRUSTED or STALE_TOLERATED.


Controlling Query Rewrite
A materialized view is only eligible for query rewrite if the ENABLE QUERY REWRITE
clause has been specified, either initially when the materialized view was first createdor subsequently with an ALTER MATERIALIZED VIEW statement.

You can set the session parameters described previously for all sessions using the
ALTER SYSTEM SET statement or in the initialization file. For a given user's session,
ALTER SESSION can be used to disable or enable query rewrite for that session only.
An example is the following:

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

You can set the level of query rewrite for a session, thus allowing different users to work at different integrity levels. The possible statements are:

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;



Accuracy of Query Rewrite
Query rewrite offers three levels of rewrite integrity that are controlled by the session parameter QUERY_REWRITE_INTEGRITY, which can either be set in your parameter
file or controlled using an ALTER SYSTEM or ALTER SESSION statement. The three
values are as follows:

■ ENFORCED
This is the default mode. The optimizer only uses fresh data from the materialized
views and only use those relationships that are based on ENABLED VALIDATED
primary, unique, or foreign key constraints.

■ TRUSTED
In TRUSTED mode, the optimizer trusts that the relationships declared in
dimensions and RELY constraints are correct. In this mode, the optimizer also uses
prebuilt materialized views or materialized views based on views, and it uses
relationships that are not enforced as well as those that are enforced. In this mode,
the optimizer also trusts declared but not ENABLED VALIDATED primary or
unique key constraints and data relationships specified using dimensions. This
mode offers greater query rewrite capabilities but also creates the risk of incorrect
results if any of the trusted relationships you have declared are incorrect.

■ STALE_TOLERATED
In STALE_TOLERATED mode, the optimizer uses materialized views that are valid
but contain stale data as well as those that contain fresh data. This mode offers the
maximum rewrite capability but creates the risk of generating inaccurate results.
If rewrite integrity is set to the safest level, ENFORCED, the optimizer uses only
enforced primary key constraints and referential integrity constraints to ensure that
the results of the query are the same as the results when accessing the detail tables
directly. If the rewrite integrity is set to levels other than ENFORCED, there are several situations where the output with rewrite can be different from that without it:

■ A materialized view can be out of synchronization with the master copy of the
data. This generally happens because the materialized view refresh procedure is
pending following bulk load or DML operations to one or more detail tables of a
materialized view. At some data warehouse sites, this situation is desirable
because it is not uncommon for some materialized views to be refreshed at certain
time intervals.
■ The relationships implied by the dimension objects are invalid. For example,
values at a certain level in a hierarchy do not roll up to exactly one parent value.
■ The values stored in a prebuilt materialized view table might be incorrect.
■ A wrong answer can occur because of bad data relationships defined by
unenforced table or view constraints


Privileges for Enabling Query Rewrite
Use of a materialized view is based not on privileges the user has on that materialized view, but on the privileges the user has on detail tables or views in the query.

The system privilege GRANT QUERY REWRITE lets you enable materialized views in
your own schema for query rewrite only if all tables directly referenced by the
materialized view are in that schema. The GRANT GLOBAL QUERY REWRITE privilege enables you to enable materialized views for query rewrite even if the materialized
view references objects in other schemas. Alternatively, you can use the QUERY
REWRITE object privilege on tables and views outside your schema.
The privileges for using materialized views for query rewrite are similar to those for definer's rights procedures.


How to Verify Query Rewrite Occurred

Because query rewrite occurs transparently, special steps have to be taken to verify
that a query has been rewritten. Of course, if the query runs faster, this should indicate that rewrite has occurred, but that is not proof. Therefore, to confirm that query rewrite does occur, use the EXPLAIN PLAN statement or the DBMS_MVIEW.EXPLAIN_
REWRITE procedure.

Read More

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.

Read More