Oracle Materialized View Enhancements

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

� In Oracle Database 10g, the ENABLE_QUERY_REWRITE parameter is TRUE by default. You must, however, ensure that the OPTIMIZER_FEATURES_ENABLE initialization parameter is set to 10.0.0 or higher.

� The QUERY_REWRITE_INTEGRITY initialization parameter still has the same default value (ENFORCED).

� You can use the following two procedures in DBMS_MVIEW:

o EXPLAIN_MVIEW This procedure tells you what kinds of query rewrites are possible. It will also tell you why a certain materialized view is not fast refreshable.

o EXPLAIN_REWRITE This procedure tells you why a query failed to rewrite. If the query rewrites, the procedure will tell you which materialized views will be used.

Using the DBMS_ADVISOR.TUNE_MVIEW procedure

The DBMS_ADVISOR.TUNE_MVIEW procedure recommends materialized views with optimized defining queries, decomposition of nonrefreshable materialized views, and fixes for materialized view log problems. It also tells you how to make a materialized view eligible for a fast refresh, if it is not.

begin
DBMS_ADVISOR.TUNE_MVIEW (:task_name,
'CREATE MATERIALIZED VIEW test_mv
REFRESH FAST WITH ROWID ENABLE QUERY REWRITE
AS SELECT DISTINCT prod_name, prod_type
From products');
end;

The preceding code will populate the new DBA_TUNE_MVIEW view.

TASK_NAME - to identify and query a particular TUNE_MVIEW recommendation.

ACTION_ID - column shows the command order number.

SCRIPT_TYPE CREATE, DROP, UNKNOWN STATEMENT - shows the recommended materialized view changes that make your materialized view eligible for a fast refresh and a query rewrite.

SELECT STATEMENT
FROM DBA_TUNE_MVIEW
WHERE TASK_NAME = :task_name
ORDER BY SCRIPT_TYPE, ACTION_ID

You can use the DBMS_ADVISOR.GET_TASK_SCRIPT procedure to output the recommendations to a text file.

Creating Materialized View Logs

One of the restrictions on the fast refresh feature is that you must include the ROWIDs of all tables that are in the FROM list in your SELECT list.

CREATE MATERIALIZED VIEW LOG ONEMPLOYEES
WITH SEQUENCE, ROWID INCLUDING NEW VALUES

Decomposing Materialized Views

TUNE_MVIEW procedure may make recommendations for the decomposition of the materialized view into two nested submaterialized views. The parent materialized view will refer to the submaterialized view that you create. This occurs in the following situations:

o A subquery in the WHERE clause

o Use of set operations like UNION, UNION ALL, INTERSECT, and MINUS

o Use of inline views

Partition Change Tracking Enhancements

Any time you change a base table�s partition scheme, the relevant materialized view rows become stale.

Oracle�s partition change tracking (PCT) feature lets you figure out which rows of a materialized view are affected by a change in a base table�s partitioning.

Oracle Database 10g extends the use of PCT to list partitioned tables, enables the use of ROWID columns as partition markers, and lets you use a PCT refresh if a materialized view contains a join-dependent expression.

The DBMS_MVIEW.REFRESH procedure has a new option, P, to indicate a forced PCT-based refresh:

DBMS_MVIEW.REFRESH(mview_name, method =>�P�)

Materialized View Execution Plans

The explain plan feature shows you whether a materialized view is being accessed as a result of a query rewrite or because you specified direct materialized view access.

Using the V$SQL_PLAN view:

Query Plan
SELECT STATEMENT
SORT ORDER BY
MATERIALIZED VIEW REWRITE ACCESS FULL EMP_INFO

If you don�t see the keyword REWRITE, it means that the materialized view was accessed directly.

The REWRITE_OR_ERROR Hint

Oracle Database 10g contains a new optimizer hint called REWRITE_OR_ERROR, which forces a query to error out if it can�t rewrite the query:

SELECT /*+ REWRITE_OR_ERROR */ ...
ORA-30393: A query block in the statement did
not rewrite

New Columns in the REWRITE_TABLE

If REWRITE_OR_ERROR raised, you can use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to find out why the query failed to rewrite.

1. Create the REWRITE_TABLE table:

<ORACLE_HOME>\RDBMS\ADMIN\utlxrw.sql

STATEMENT_ID	ID for the query
MV_OWNER	MV's schema
MV_NAME	Name of the MV
SEQUENCEINTEGER	Seq # of error msg
QUERY	user query
MESSAGE	EXPLAIN_REWRITE error msg
PASS	Query Rewrite pass no
MV_IN_MSG	MV in current message
MEASURE_IN_MSG	Measure in current message
JOIN_BACK_TBL	Join back table in current msg
JOIN_BACK_COL	Join back column in current msg
ORIGINAL_COST INTEGER	Cost of original query
REWRITTEN_COST	Cost of rewritten query. It shows a zero if there was no rewrite of a query or if a different materialized view was used
FLAGS	Associated flags

2. Execute DBMS_MVIEW.EXPLAIN_REWRITE:

DBMS_MVIEW.EXPLAIN_REWRITE ('SELECT
p.prod_name, SUM(amount_sold).. ',
'TestXRW.PRODUCT_SALES_MV', 'SH')
SELECT message FROM rewrite_table ORDER BY
sequence;
MESSAGE
--------------------------------------------
QSM-01033: query rewritten with materialized
view, PRODUCT_SALES_MV

Materialized Join View Enhancements

Materialized join views (MJVs) contain only joins (and not aggregates).

For a fast refresh of materialized join views � whether they use self joins, inline views, or remote tables � you must create materialized view logs on each of the base tables. The materialized view logs must also contain the ROWID column.

Partition Maintenance Operations

In Oracle Database 10g, you can issue commands that truncate, exchange, or drop partitions by using the ALTER MATERIALIZE VIEW statement.

Materialized View Refresh Using Trusted Constraints

If you use the TRUSTED option, the resulting materialized views are in an unknown state, and you can use them for a query rewrite in a TRUSTED or a STALE_TOLERATED mode only.