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.

Posted in: Query Rewrite