Synopsis. Oracle 10g Release 2 (10gR2) has improved the effectiveness and speed of materialized view refreshes, something that is becoming especially important in increasingly complex data warehouses and data mart environments. This article – the first in a series – demonstrates several new features of materialized views in Oracle 10gR2, including enhancements to join-only materialized views, new optimizer hints for improved QUERY REWRITE analysis, and improved materialized view debugging tools.
Materialized views have been available in one form or another since before Oracle 8i, when they were called snapshots, and they can appear somewhat mysterious if you haven’t yet encountered one because your current database environment hasn’t yet demonstrated a need (e.g. data warehousing). The simplest way to think of a materialized view is that it’s a view that references an underlying table, and that table contains data that can be refreshed automatically on a periodic scheduled basis, refreshed manually by a call to an Oracle supplied package called DBMS_MVIEW, or even decide to refresh itself whenever rows in other source tables change.
The true strength of a materialized view becomes extremely apparent in a data warehouse, a data mart, or even a hybrid environment. For example, I can build a materialized view that describes which employees are currently assigned to various roles within the company’s hierarchical organization structure; when an employee changes roles, or a new employee is added to the hierarchy, the materialized view will detect these modifications and thus decide to refresh itself.
Materialized Join View (MJV) Enhancements
Oracle 10gR2 has finally removed some limits imposed as far back as Oracle 9i on the performance and flexibility of materialized view refresh mechanisms. One previous limitation that has been removed is that materialized views that contained joins only – also known as MJVs – can now be refreshed quickly and automatically. However, there are still a few limitations and restrictions on MJVs:
ROWIDs Required for Fast Refresh. If the FROM clause of the MJV does contain more than one table, then every table in the MJV must specify a ROWID column in the SELECT list, and the materialized view logs for the MJV must also contain the ROWID for those tables.
View Merging Must Be Permitted. If the MJV contains any named or inline views, then Oracle must be permitted to perform view merging; once view merging is complete, the resulting materialized view must satisfy all requirements for fast refresh as mentioned previously.
Remote Tables Must Be From Same Site. An MJV is now permitted to contain references to remote tables, which are tables that are accessed via a public or private database link. If the FROM clause of the MJV does reference any remote tables, then all the tables in the FROM clause must be located at the same site. Also, note that the ROWID rules mentioned above also hold true if the MJV is to be fast-refreshed.
Listing 1.1 shows sample code that demonstrates how to create a materialized join view and its required materialized view logs to take advantage of these new features while still hewing to the aforementioned restrictions.
Improvements to Materialized View Execution Plan Debugging
One of the more trying tasks I’ve faced when stepping into the role of development DBA is attempting to tune an errant query that I fully expected Oracle should have rewritten to use a materialized view. In other words, if I create a query against the tables in the materialized view that returns the same result set that the materialized view will return, Oracle should execute the query against the materialized view instead of those base tables. Since the materialized view is usually much smaller in size than the base tables, this means that fewer resources will be used to return the results to the query.
However, in many cases Oracle may simply appear to refuse to rewrite a query against a materialized view. There are several reasons that this may occur, but they can be esoteric and difficult to debug in a timely fashion. Fortunately, Oracle 10g now provides several new tools and features to help resolve query rewrite issues:
ENABLE_QUERY_REWRITE Defaults to TRUE. I can’t count the number of times I have helped a developer to debug a materialized view that wouldn’t rewrite, only to find the developer had forgotten to set the ENABLE_QUERY_REWRITE initialization parameter to TRUE on her database. In Oracle 10g, this parameter is set to TRUE by default; it had been FALSE by default in earlier releases.
REWRITE_OR_ERROR Hint. Since I expect a query that is executed against a materialized view to be rewritten, it would be nice to have Oracle tell me when a QUERY REWRITE operation has failed unexpectedly. Oracle 10g provides a new optimizer hint called REWRITE_OR_ERROR that will return an ORA-30393 error whenever the optimizer determines that query cannot be rewritten. I can then trap this error as a named exception and proceed to notify the appropriate developer or DBA so that the issue can be investigated and resolved.
EXPLAIN PLAN Upgrades. Finally, Oracle 10g at last corrects a minor but nonetheless important discrepancy in the contents stored in PLAN_TABLE, the table that is necessary for capturing and retaining information from an EXPLAIN PLAN FOR STATEMENT command. When a query’s access plan can utilize a materialized view instead of one or more base tables, Oracle 10g now properly records this fact by labeling the access path as a MATERIALIZED VIEW. (Prior releases will display the access path as a normal TABLE hint instead.)
Listing 1.2 shows two example queries that access the new materialized join view created in Listing 1.1, and the resulting query plans that are produced. Note that I’m using the DBMS_XPLAN.DISPLAY procedure to create a PL/SQL collection that is then cast into a TABLE format so that I can issue a SELECT statement against it to view the EXPLAIN PLAN output. This is now Oracle’s suggested method for formatting access plan information.
This listing also shows what happens when I apply the REWRITE_OR_ERROR hint on a simple query that uses almost all of the columns in the HR.MJV_LOC_DEPT_EMPS materialized view. Because the query references the STATE_PROVINCE column in the HR.LOCATIONS table, the query cannot be rewritten, and therefore generates an ORA-30393 error.
Demystifying QUERY REWRITEs With DBMS_MVIEW
Yet another valuable tool for debugging failed or poorly performing query rewrite operations is Oracle’s DBMS_MVIEW.EXPLAIN_REWRITE procedure. This procedure populates a special table named REWRITE_TABLE that will contain detailed information about how a query rewrite is operating. Oracle 10g has added several new columns to REWRITE_TABLE to facilitate deeper analysis of why a query failed to rewrite, including information on the relative costs of the original vs. rewritten SQL.
These new MV debugging features are amply demonstrated in Listing 1.3, which shows how to populate REWRITE_TABLE when a query rewrite operation performs sub-nominally. Note that I executed the utlxrw.sql script (found in $ORACLE_HOME/rdbms/admin) to create REWRITE_TABLE before executing DBMS_MVIEW.EXPLAIN_REWRITE to populate the table.
Listing 1.4 shows one additional new debugging tool, DBMS_MVIEW.EXPLAIN_MVIEW. This new procedure analyzes all potential capabilities of a selected materialized view and returns a list of those operations (e.g., FAST REFRESH) that the materialized view will currently support. As with DBMS_MVIEW.EXPLAIN_REWRITE, note that I had to execute the utlxmv.sql script (also found in $ORACLE_HOME/rdbms/admin) to create the MV_CAPABILITIES table before executing DBMS_MVIEW.EXPLAIN_MVIEW to populate the table.
Proactively Tuning MVs Using DBMS_ADVISOR.TUNE_MVIEW
Even though these new tools and features are helpful for honing in on exactly why queries against an MV are not triggering query rewrite operations, what I’ve hoped for is a method to perform proactive tuning of MVs. The good news is that Oracle 10g provides a new Advisor task called DBMS_ADVISOR.TUNE_MVIEW to which I can submit the text of a proposed MV for scrutiny.
Once I have submitted an Advisor task for scrutiny, DBMS_ADVISOR.TUNE_MVIEW will return advice on modifications that will increase the performance of a sub-optimal materialized view. For example, this Advisor may suggest splitting the MV into two sub-MVs or even recommend adding additional MV logs to help the MV to perform more effectively.
In Listing 1.5, I’ve created an example Advisor task that asks DBMS_ADVISOR.TUNE_MVIEW to perform its magic against a few materialized views that I suspect are candidates for tuning. I’ve also shown the results of that analysis. Note that I executed the utlxrw.sql script (found in $ORACLE_HOME/rdbms/admin) to create table REWRITE_TABLE before executing DBMS_ADVISOR.TUNE_MVIEW.
Trust, But Verify: Materialized View Refresh Using Trusted Constraints
Oracle 10g adds one more powerful feature to MV refreshes: the ability of a materialized view to choose more query rewrite options, generally resulting in better and more efficient execution of refreshes, via the USING TRUSTED CONSTRAINTS clause.
For example, if I create a constraint against a column in ENABLE NOVALIDATE mode, that constraint will only be applied to any new or updated values for that column, and Oracle will not validate any pre-existing values for that column. If I am willing to take responsibility for the validity of the pre-existing data, I can specify the keyword RELY for the constraint. When a materialized view utilizes this data during query rewrite to determine the best access path for join operations, it assumes that the data in that column is valid.
The USING TRUSTED CONSTRAINTS clause tells Oracle to use dimension and constraint information that the DBA has declared trustworthy, but that the database has not yet validated. It is therefore likely that the materialized view’s refresh performance may improve as long as the dimension and constraint information is valid. On the other hand, if Oracle determines that this information is invalid, the refresh procedure may instead corrupt the materialized view even though the refresh operation itself returns a successful status. (If USING TRUSTED CONSTRAINTS is not specified, Oracle will use the default method, USING ENFORCED CONSTRAINTS, during the refresh operation.)
I have constructed three new tables and a new materialized view to represent the start of a sales force assignment subsystem. (It may appear that I’ve gone a long way to demonstrate a simple concept, but I’ll be using these objects extensively in the next article.) Listing 1.6 illustrates how to employ the USING TRUSTED CONSTRAINTS clause in a materialized view in an attempt to improve its performance. Note that the UNKNOWN_TRUSTED_FD column of the DBA_MVIEWS data dictionary view will be marked (Y)es when a trusted constraint has been applied to a materialized view.
Next Steps
As impressive as these materialized view enhancements are, the good news about new features for materialized views doesn’t end here. In my next article, I’ll delve into using Partition Change Tracking (PCT) and Partitioned Maintenance Operations (PMOPs), two new sets of features that allow a DBA to isolate updates to only those partitions of a materialized view affected by the refresh operation.
References and Additional Reading
Even though I’ve hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I’ve drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:
B14200-02 Oracle Database SQL Reference
B14214-01 Oracle Database New Features Guide
B14223-02 Oracle Database Data Warehousing Guide
B14231-01 Oracle Database Administrator’s Guide
B14237-02 Oracle Database Reference
B14258-01 PL/SQL Packages and Types Reference
