11 Basic Query Rewrite for Materialized Views
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:
-
SELECT
-
CREATE TABLE … AS SELECT
-
INSERT INTO … SELECT
It also operates on subqueries in the set operators UNION
, UNION ALL
, INTERSECT
, INTERSECT ALL
, EXCEPT
, EXCEPT ALL
, MINUS
, and MINUS ALL
, and subqueries in DML statements such as INSERT
, DELETE
, and UPDATE
.
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 REWRITE
and NOREWRITE
hints and the QUERY_REWRITE_ENABLED
session parameter.
The 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?
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 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
You can use the DBMS_ADVISOR.TUNE_MVIEW
procedure to optimize a CREATE
MATERIALIZED
VIEW
statement to enable general QUERY
REWRITE
.
11.2.1 Enabling Query Rewrite for Materialized Views
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;
Note:
Both Oracle join syntax (shown above) and ANSI join syntax are supported.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.
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 |
---|---|---|
OPTIMIZER_MODE |
ALL_ROWS (default), FIRST_ROWS , or FIRST_ROWS_ n |
With |
QUERY_REWRITE_ENABLED |
TRUE (default), FALSE , or FORCE |
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 |
QUERY_REWRITE_INTEGRITY |
STALE_TOLERATED , TRUSTED , or ENFORCED (the default)
|
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 |
Related Topics
11.2.3 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 created or 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; 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 QUERY_REWRITE_INTEGRITY
.
The values that you can set for the QUERY_REWRITE_INTEGRITY
parameter 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 andRELY
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 notENABLED 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 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 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.
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(+);
Note:
Both Oracle join syntax (shown above) and ANSI join syntax are supported.join_sales_time_product_mv
:
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 EXPLAIN
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;
Note:
Both Oracle join syntax and ANSI join syntax now supported. For example, the previous query could be written using the ANSI syntax as follows.SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM sales s JOIN times t ON 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.