Using Materialized Views to Alter Oracle Query Execution Paths

For a large database, a materialized view may offer several performance benefits. You can use materialized views to influence the optimizer to change the execution paths for queries. This feature, called query rewrite, enables the optimizer to use a materialized view in place of the table queried by the materialized view, even if the materialized view is not named in the query. For example, if you have a large SALES table, you may create a materialized view that sums the SALES data by region. If a user queries the SALES table for the sum of the SALES data for a region, Oracle can redirect that query to use your materialized view in place of the SALES table. As a result, you can reduce the number of accesses against your largest tables, improving system performance. Further, because the data in the materialized view is already grouped by region, any summarization is already complete by the time the query is issued.

NOTE

You must specify ENABLE QUERY REWRITE in the materialized view definition for the view to be used as part of a query rewrite operation.

To use the query rewrite capability effectively, you should create a dimension that defines the hierarchies within the table’s data. To execute the CREATE DIMENSION command, you need to have been granted the CREATE DIMENSION system privilege. For instance, you can create a dimension that supports the hierarchy between the COUNTRY and CONTINENT sample tables:

To enable a materialized view for query rewrite, all of the master tables for the materialized view must be in the materialized view’s schema, and you must have the QUERY REWRITE system privilege. If the view and the tables are in separate schemas, you must have the GLOBAL QUERY REWRITE system privilege. In general, you should create materialized views in the same schema as the tables on which they are based; otherwise, you will need to manage the permissions and grants required to create and maintain the materialized view.

You can enable or disable query rewrite at the SQL statement level via the REWRITE and NOREWRITE hints. When using the REWRITE hint, you can specify materialized views for the optimizer to consider.

NOTE

Query rewrite decisions are based on the costs of the different execution paths, so your statistics should be kept up to date.

For query rewrite to be possible, you must set the following initialization parameters:

OPTIMIZER_MODE = ALL_ROWS or FIRST_ROWS

 QUERY_REWRITE_ENABLED = TRUE

 QUERY_REWRITE_INTEGRITY = STALE_TOLERATED, TRUSTED, or ENFORCED

By default, QUERY_REWRITE_INTEGRITY is set to ENFORCED; in this mode, all constraints must be validated. The optimizer only uses fresh data from the materialized views and only uses those relationships that are based on ENABLED VALIDATED primary, unique, or foreign key constraints. In TRUSTED mode, the optimizer trusts that the data in the materialized view is fresh and the relationships declared in dimensions and constraints are correct. In STALE_TOLERATED mode, the optimizer uses materialized views that are valid but contain stale data, as well as those that contain fresh data.

If you set QUERY_REWRITE_ENABLED to FORCE, the optimizer rewrites queries to use materialized views even when the estimated query cost of the original query is lower.

If query rewrite occurs, the explain plan for the query will list the materialized view as one of the objects accessed, along with an operation listed as “MAT_VIEW REWRITE ACCESS.” You can use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to see if rewrite is possible for a query, and which materialized views would be involved. If the query cannot be rewritten, the procedure will document the reasons.

EXPLAIN_REWRITE takes three input parameters—the query, a materialized view name, and a statement identifier—and can store its output in a table. Oracle provides the CREATE TABLE command for the output table in a script named utlxrw.sql in the ./rdbms/admin directory under the Oracle software home directory. The utlxrw.sql script creates a table named REWRITE_TABLE.

You can query the REWRITE_TABLE for the original cost, rewritten cost, and the optimizer’s decision. The MESSAGE column displays the reasons for the optimizer’s decision.