Oracle9iR2 Data Warehousing

4.4 EXPLAIN_MVIEW utility

As we have seen so far, in order to get the most benefit out of a materialized view, the materialized view must conform to some rules. For instance, you may need to create materialized view logs to make fast refresh possible or add a partition marker to allow partition change tracking. The EXPLAIN_MVIEW interface is designed to help you determine what these rules are. Before creating the materialized view, you can run its defining query through the EXPLAIN_MVIEW utility. If a capability such as fast refresh is disallowed, EXPLAIN_MVIEW will point out the offending construct or missing columns. You now have the opportunity to fix these problems before expending precious resources to create the materialized view. This facility is new in Oracle 9i. EXPLAIN_MVIEW can also tell you what features are available with your existing materialized view.

EXPLAIN_MVIEW is invoked via the PL/SQL API DBMS_MVIEW.EXPLAIN_MVIEW and comes in two flavors. You can provide an existing materialized view name to it and display its capabilities. Alternatively, you can provide a SELECT statement that defines your intended materialized view, and EXPLAIN_MVIEW will project its capabilities. The results of EXPLAIN_MVIEW are placed in the MV_CAPABILITIES_TABLE table, which must be created by running the utlxmv.sql script found in the rdbms/admin directory.

In the following example, we have deliberately introduced problems with the materialized view logs to illustrate how EXPLAIN_MVIEW can be used. We have dropped the materialized view log on product and removed the INCLUDING NEW VALUES clause from the materialized view log on time.

ALTER MATERIALIZED VIEW LOG on TIME EXCLUDING NEW VALUES; DROP MATERIALIZED VIEW LOG on PRODUCT; BEGIN dbms_mview.explain_mview ( 'SELECT t.month, t.year, p.product_id, SUM (f.purchase_price) as sum_of_sales, COUNT (f.purchase_price) as total_sales, COUNT(*) as cstar FROM time t, product p, purchases f WHERE t.time_key = f.purchase_date AND f.product_id = p.product_id GROUP BY t.month, t.year, p.product_id'); END; /

The output of EXPLAIN_MVIEW first lists all the capabilities of a materialized view and whether each one is possible (Y) or not (N). It then presents detailed information regarding each capability and the reason, if any, why it is not possible.

SELECT capability_name, possible p, related_text table, msgtxt explanation FROM MV_CAPABILITIES_TABLE; CAPABILITY_NAME P TABLE EXPLANATION ----------------------------- - ----- ---------------------- PCT N REFRESH_COMPLETE Y REFRESH_FAST N REWRITE Y REWRITE_FULL_TEXT_MATCH Y REWRITE_PARTIAL_TEXT_MATCH Y REWRITE_GENERAL Y PCT_TABLE N TIME relation is not a partitioned table PCT_TABLE N PRODUCT relation is not a partitioned table PCT_TABLE N PURCHASES no partition key or PMARKER in select list REFRESH_FAST_AFTER_INSERT N PRODUCT the detail table does not have a materialized view log REFRESH_FAST_AFTER_INSERT N TIME mv log must have new values REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_ INSERT is disabled REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ ONETAB_DML is disabled REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view REWRITE_PCT N PCT is not possible on any of the detail tables in the materialized view

From this output we can see that the PCT_TABLE capability for PURCHASES table is not possible, because that table is partitioned but its partition key (or partition marker) was not included in the materialized view. The TIME table is not partitioned and hence PCT refresh is not possible if it is updated.

Fast refresh may be possible in some situations and not others. Hence, the fast refresh capabilities are presented at three levels, so if the first one is not possible, the remaining ones are also not possible and so on:

• REFRESH_AFTER_INSERT (only inserts were done to the tables)

• REFRESH_AFTER_ONETABDML (only one table is modified at a time)

• REFRESH_AFTER_ANY_DML (there is no restriction on the type of DML)

The output of EXPLAIN_MVIEW shown here indicates that the mate- rialized view log on the TIME table was missing the INCLUDING NEW VALUES clause and hence fast refresh using materialized view logs is not possible.

EXPLAIN_MVIEW also indicates if query rewrite has been enabled and if it is possible in general or only using text match mode.

Let us now correct the problems with the materialized view logs and rerun the utility. You can see that fast refresh is now possible, as indicated by REFRESH_FAST_AFTER_INSERT Y.

CREATE MATERIALIZED VIEW LOG on product WITH ROWID, SEQUENCE (product_id) INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG on TIME including new values; BEGIN dbms_mview.explain_mview ( 'SELECT t.month, t.year, p.product_id, SUM (f.purchase_price) as sum_of_sales, COUNT (f.purchase_price) as total_sales, COUNT(*) as cstar FROM time t, product p, purchases f WHERE t.time_key = f.purchase_date AND f.product_id = p.product_id GROUP BY t.month, t.year, p.product_id'); END; / SELECT capability, possible FROM MV_CAPABILITIES_TABLE WHERE capability_name LIKE 'REFRESH%'; CAPABILITY_ P ------------------------------ - REFRESH_COMPLETE Y REFRESH_FAST Y REFRESH_FAST_AFTER_INSERT Y REFRESH_FAST_AFTER_ONETAB_DML Y REFRESH_FAST_AFTER_ANY_DML Y REFRESH_FAST_PCT N

If you wanted to look at the capabilities of an existing materialized view, for example, MONTHLY_SALES_MV—you would use EXPLAIN_MVIEW, as follows:

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('EASYDW.MONTHLY_SALES_MV');

EXPLAIN_MVIEW also has interfaces to return the results in a PL/SQL array rather than in a table.

The CREATE MATERIALIZED VIEW screen in Oracle Enterprise Manager (Figure 4.1) has an Explain button, which can be clicked to run EXPLAIN_MVIEW. This allows you to immediately identify and correct any problems with the materialized view. Figure 4.5 is the OEM screen showing the output of EXPLAIN_MVIEW.

Figure 4.5: EXPLAIN_MVIEW in Oracle Enterprise Manager

You can also issue EXPLAIN_MVIEW on an existing materialized view by using the Explain button shown in Figure 4.2.