Configuring your Oracle9i database for materialized views

In the previous article, we discussed materialized views, a feature introduced in Oracle9i to allow very fast dynamic creation of complex objects. Materialized views allow subsecond response times by precomputing aggregate information, and Oracle9i dynamically rewrites SQL queries to reference existing materialized views. In this article, we continue our discussion of materialized views and discuss how to set up and configure your Oracle database to use this powerful new feature. We begin with a look at the initialization parameters and continue with details of the effective management and use of materialized views.

Prerequisites for using materialized views
In order to use materialized views, the Oracle DBA must set special initialization parameters and grant special authority to the users of materialized views. You start by setting these initialization parameters within Oracle to enable the mechanisms for materialized views and query rewrite, as shown in Listing A.

Note that if the CURSOR_SHARING parameter is set to FORCE, some rewrites that were text-match based in 8.1.5 may no longer be possible with 8.1.6. There are three acceptable values for query_rewrite_integrity:

trusted—Assumes that the materialized view is current

enforced (default)—Always goes to materialized view with fresh data

stale_tolerated—Uses materialized view with both stale and fresh data

Next, you must grant several system privileges to all users who will be using the materialized views. In many cases, the Oracle DBA will encapsulate these grant statements into a single role and grant the role to the end users:

grant query rewrite to scott;
grant create materialized view to scott;
alter session set query_rewrite_enabled = true;

Invoking SQL query rewrite
Once materialized views have been enabled, Oracle provides several methods for invoking query rewrite. Query rewrite is generally automatic, but you can explicitly enable it by using Isession, alter system, or SQL hints:

ALTER {SESSION|SYSTEM} DISABLE QUERY REWRITE

Select /*+REWRITE(mv1)*/...

Refreshing materialized views
In Oracle9i, if you specify REFRESH FAST for a single-table aggregate materialized view, you must have created a materialized view log for the underlying table, or the refresh command will fail. When creating a materialized view, you have the option of specifying whether the refresh occurs manually (ON DEMAND) or automatically (ON COMMIT, DBMS_JOB). To use the fast warehouse refresh facility, you must specify the ON DEMAND mode. To refresh the materialized view, call one of the procedures in DBMS_MVIEW.

The DBMS_MVIEW package provides three types of refresh operations:

DBMS_MVIEW.REFRESH—Refreshes one or more materialized views

DBMS_MVIEW.REFRESH_ALL_MVIEWS—Refreshes all materialized views

DBMS_MVIEW.REFRESH_DEPENDENT—Refreshes all table-based materialized views

Manual complete refresh
A complete refresh occurs when the materialized view is initially defined, unless it references a prebuilt table, and a complete refresh may be requested at any time during the life of the materialized view. Because the refresh involves reading the detail table to compute the results for the materialized view, this can be a very time-consuming process, especially if huge amounts of data need to be read and processed.

Manual fast (incremental) refresh
If you specify REFRESH FAST (which means that only deltas performed by UPDATE, INSERT, DELETE on the base tables will be refreshed), Oracle performs further verification of the query definition to ensure that fast refresh can always be performed if any of the detail tables change. These additional checks include the following:

A materialized view log must be present for each detail table.

The RowIDs of all the detail tables must appear in the SELECT list of the MVIEW query definition.

If there are outer joins, unique constraints must be placed on the join columns of the inner table.

You can use the DBMS_MVIEW package to manually invoke either a fast refresh or a complete refresh, where F equals Fast Refresh and C equals Complete Refresh:

EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F');

Automatic fast refresh of materialized views
The automatic fast refresh feature is completely new in Oracle9i, so you can refresh a snapshot with DBMS_JOB in a short interval according to the snapshot log. With Oracle 9i, it's possible to refresh automatically on the next COMMIT performed at the master table. This ON COMMIT refreshing can be used with materialized views on single-table aggregates and materialized views containing joins only. ON COMMIT MVIEW logs must be built as ROWID logs, not as primary-key logs. For performance reasons, it's best to create indexes on the ROWIDs of the MVIEW. Note that the underlying table for the MVIEW can be prebuilt.

Listing B provides an example of a materialized view with an ON COMMIT refresh.

Creating a materialized view
To see all the steps in the creation of a materialized view, let’s take it one step at a time. The code for each step is shown in Listing C:

1. Set the initialization parameters and bounce the database.
2. Create the materialized view table. Here, we specify that the materialized view will be refreshed every two hours with the refresh fast option. Instead of using DBMS_MVIEW, you can automatically refresh the MVIEW (Snapshot) using Oracle DBMS_JOB Management.
3. Create the optimizer statistics and refresh the materialized view.
4. Test the materialized view.
5. Create the MVIEW log(s) MATERIALIZED VIEW.
6. Execute a manual complete refresh.

Monitoring materialized views
Oracle provides information in the data dictionary to monitor the behavior of materialized views. When you’re monitoring materialized views, it’s critical that you check the refresh interval in the dba_jobs view. Here is a SQL statement to check the generated job status for materialized views:

SELECT
  SUBSTR(job,1,4) "Job", SUBSTR(log_user,1,5)          "User",
  SUBSTR(schema_user,1,5)                              "Schema",
  SUBSTR(TO_CHAR(last_date,'DD.MM.YYYY HH24:MI'),1,16) "Last Date",
  SUBSTR(TO_CHAR(next_date,'DD.MM.YYYY HH24:MI'),1,16) "Next Date",
  SUBSTR(broken,1,2) "B", SUBSTR(failures,1,6)        "Failed",
  SUBSTR(what,1,20)                                    "Command"
FROM dba_jobs;
Job  User  Schem Last Date         Next Date       B Fail Command
---- ----- ----- ---------------- ---------------- - ---- -----------------90   SCOTT SCOTT 28.01.2000 11:33 28.01.2000 13:33 N 0    dbms_refresh.refresh

Conclusion
Oracle materialized views are quite complex in nature and require a significant understanding to be used effectively. In this article, I covered the required set-up methods and the steps for creating materialized views and appropriate refresh intervals.