This chapter discusses query rewrite in Oracle, and contains:
11.1 Overview of Query Rewrite
When base tables contain large amount of data, it is expensive and time-consuming to compute the required aggregates or to compute joins between these tables. In such cases, queries can take minutes or even hours. Because materialized views contain already precomputed aggregates and joins, Oracle Database 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. "When Does Oracle Rewrite a Query?" describes the conditions that must be met for a query to be rewritten.
11.1.1 About Query Rewrite and the Optimizer
A query undergoes several checks to determine whether it is a candidate for query rewrite.
If the query fails any check, then the query is applied to the detail tables rather than the materialized view. The inability to rewrite can be costly in terms of response time and processing power.
The optimizer uses two different methods to determine when to rewrite a query in terms of a materialized view. The first method matches the SQL text of the query to the SQL text of the materialized view definition. If the first method fails, then 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:
CREATE TABLE … AS SELECT
INSERT INTO … SELECT
It also operates on subqueries in the set operators
UNION ALL ,
MINUS ALL, and subqueries in DML statements such as
Dimensions, constraints, and rewrite integrity levels affect whether a query is rewritten to use materialized views. Additionally, query rewrite can be enabled or disabled by
NOREWRITE hints and the
QUERY_REWRITE_ENABLED session parameter.
DBMS_MVIEW.EXPLAIN_REWRITE procedure advises whether query rewrite is possible on a query and, if so, which materialized views are used. It also explains why a query cannot be rewritten.
11.1.2 When Does Oracle Rewrite a Query?
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 test these conditions, the optimizer may depend on some of the data relationships declared by the user using constraints and dimensions, among others, hierarchies, referential integrity, and uniqueness of key data, and so on.
11.2 Ensuring that Query Rewrite Takes Effect
- Individual materialized views must have the
If this step is not completed, as described in Enabling Query Rewrite for Materialized Views, then a materialized view is never eligible for query rewrite.
- The session parameter
QUERY_REWRITE_ENABLEDmust be set to
TRUE(the default) or
- Cost-based optimization must be used by setting the initialization parameter
You can use the
DBMS_ADVISOR.TUNE_MVIEW procedure to optimize a
VIEW statement to enable general
11.2.1 Enabling Query Rewrite for Materialized Views
You can specify
REWRITE either with the
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;
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.
11.2.2 About Initialization Parameters for Query Rewrite
Query rewrite behavior is controlled by certain database initialization parameters.
Table 11-1 Initialization Parameters that Control Query Rewrite Behavior
|Initialization Parameter Name||Initialization Parameter Value||Behavior of Query Rewrite|
This option enables the query rewrite feature of the optimizer, enabling the optimizer to utilize materialized views to enhance performance. If set to
If set to
This parameter is optional. However, if it is set, the value must be one of these specified in the Initialization Parameter Value column.
By default, the integrity level is set to
11.2.3 Controlling Query Rewrite
A materialized view is only eligible for query rewrite if the
REWRITE clause has been specified, either initially when the materialized view was first created or subsequently with an
You can set the session parameters described previously for all sessions using the
SET statement or in the initialization file. For a given user's session,
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; ALTER SESSION SET QUERY_REWRITE_INTEGRITY = ENFORCED;
11.2.4 About the Accuracy of Query Rewrite
Query rewrite offers three levels of rewrite integrity that are controlled by the initialization parameter
The values that you can set for the
QUERY_REWRITE_INTEGRITY parameter are as follows:
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 VALIDATEDprimary, unique, or foreign key constraints.
TRUSTEDmode, the optimizer trusts that the relationships declared in dimensions and
RELYconstraints 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. It also trusts declared but not
ENABLED VALIDATEDprimary 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_TOLERATEDmode, 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 primary 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.
You can set
QUERY_REWRITE_INTEGRITY either in your initialization parameter file or using an
ALTER SYSTEM or
ALTER SESSION statement.
11.2.5 About 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
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
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
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.
11.2.6 Sample Schema and Materialized Views
The following sections use the
sh sample schema and a few materialized views to illustrate how the optimizer uses data relationships to rewrite queries.
The query rewrite examples in this chapter mainly refer to the following materialized views. These materialized views do not necessarily represent the most efficient implementation for the
sh schema. Instead, they are a base for demonstrating rewrite capabilities. Further examples demonstrating specific functionality can be found throughout this chapter.
The following materialized views contain joins and aggregates:
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_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_subcategory, t.week_ending_day; CREATE MATERIALIZED VIEW sum_sales_prod_week_mv ENABLE QUERY REWRITE AS SELECT p.prod_id, t.week_ending_day, s.cust_id, SUM(s.amount_sold) AS sum_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_id, t.week_ending_day, s.cust_id; 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;
The following materialized views contain joins only:
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; CREATE MATERIALIZED VIEW join_sales_time_product_oj_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(+);
Although it is not a strict requirement, it is highly recommended that you collect statistics on the materialized views so that the optimizer can determine whether to rewrite the queries. You can do this either on a per-object base or for all newly created objects without statistics. The following is an example of a per-object base, shown for
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ( - 'SH','JOIN_SALES_TIME_PRODUCT_MV', estimate_percent => 20, - block_sample => TRUE, cascade => TRUE);
The following illustrates a statistics collection for all newly created objects without statistics:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ( 'SH', - options => 'GATHER EMPTY', - estimate_percent => 20, block_sample => TRUE, - cascade => TRUE);
11.2.7 How to Verify if 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
PLAN statement or the
DBMS_MVIEW.EXPLAIN_REWRITE procedure. See "Verifying that Query Rewrite has Occurred" for further information.
11.3 Example of Query Rewrite
This example illustrates the power of query rewrite with materialized views.
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 REWRITE 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_month_desc;
Let us assume that, in a typical month, the number of sales in the store is around one million. So this materialized aggregate view has the precomputed aggregates for the dollar amount sold for each month.
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 Database must 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 dozen rows in the materialized view
cal_month_sales_mv and no joins, Oracle Database returns the results instantly.