Database Reference
In-Depth Information
The WITH ROWID clause is added to specify how rows are identified in the materialized view log—that is, how
to identify the base table row whose modification is tracked by each materialized view log row. It's also possible to
create materialized view logs that identify rows with the primary key or the object ID. However, for the purposes of
this chapter, the rows have to be identified by their rowid (the others are useful when materialized views are used in
distributed environments).
Just as materialized views have an associated container table, every materialized view log also has an associated
table where the modifications to the base table are logged. The following query shows how to display its name:
SQL> SELECT master, log_table
2 FROM dba_mview_logs
3 WHERE master IN ('SALES', 'CUSTOMERS', 'PRODUCTS')
4 AND log_owner = 'SH';
MASTER LOG_TABLE
--------- ---------------
CUSTOMERS MLOG$_CUSTOMERS
PRODUCTS MLOG$_PRODUCTS
SALES MLOG$_SALES
For some materialized views, such a basic materialized view log isn't enough to support fast refreshes. There are
additional requirements to be fulfilled. Because these requirements are strongly dependent on the query associated
with the materialized view and the Oracle Database version, instead of providing a list, I show you how to find out,
given a specific case, what these requirements are. To do this, you can use the same method used to find out what
the supported query rewrite modes are (see the earlier section “Query Rewrite”). In other words, you can use the
explain_mview procedure in the dbms_mview package, as shown in the following example:
SQL> execute dbms_mview.explain_mview(mv => 'sales_mv', stmt_id => '42')
The output of the procedure is provided in the mv_capabilities_table table. To see whether the materialized
view can be fast-refreshed, you can use a query like the following. Notice that in the output, the possible column is
always set to N . This means that no fast refresh is possible. In addition, the msgtxt and related_text columns indicate
the cause of the problem:
SQL> SELECT capability_name, possible, msgtxt, related_text
2 FROM mv_capabilities_table
3 WHERE statement_id = '42'
4 AND capability_name LIKE 'REFRESH_FAST_AFTER%';
CAPABILITY_NAME POSSIBLE MSGTXT RELATED_TEXT
----------------------------- -------- ------------------------------- -------------
REFRESH_FAST_AFTER_INSERT N mv log must have new values SH.PRODUCTS
REFRESH_FAST_AFTER_INSERT N mv log does not have all necess SH.PRODUCTS
ary columns
REFRESH_FAST_AFTER_INSERT N mv log must have new values SH.CUSTOMERS
REFRESH_FAST_AFTER_INSERT N mv log does not have all necess SH.CUSTOMERS
ary columns
REFRESH_FAST_AFTER_INSERT N mv log must have new values SH.SALES
REFRESH_FAST_AFTER_INSERT N mv log does not have all necess SH.SALES
ary columns
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr) AMOUNT_SOLD
 
Search WWH ::




Custom Search