This chapter discusses query rewrite in Oracle, and contains:
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.
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:
It also operates on subqueries in the set operators
MINUS, and subqueries in DML statements such as
Dimensions, constraints, and rewrite integrity levels affect whether or not a given query is rewritten to use one or more 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 will be used. It also explains why a query cannot be rewritten.
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.
Individual materialized views must have the
The session parameter
QUERY_REWRITE_ENABLED must be set to
TRUE (the default) or
Cost-based optimization must be used by setting the initialization parameter
If step 1 has not been completed, a materialized view will never be eligible for query rewrite. 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.
You can use the
DBMS_ADVISOR.TUNE_MVIEW procedure to optimize a
VIEW statement to enable general
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
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).
This option enables the query rewrite feature of the optimizer, enabling the optimizer to utilize materialized views 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 unrewritten query is lower.
This parameter is optional, but must be set to
ENFORCED (the default) if it is specified (see "Accuracy of Query Rewrite").
By default, the integrity level is set to
ENFORCED. In this mode, all constraints must be validated. Therefore, if you use
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
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;
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
SESSION statement. The three values 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
VALIDATED primary, unique, or foreign key constraints.
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. It also trusts declared but not
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 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.
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.
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);
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.
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!