Oracle: Create a Materialized View with Fast Refresh on commit and debug upcoming errors

Sometimes, our users and customers need rapid access to complex data structures. Materialized Views in their various forms are a very potent way to shift efforts from the query to insert/update time of base data. That’s the nature of a Materialized View: One way or another, we need to put the data into a redundant structure. With Fast Refresh, this way goes through Materialized View Logs, causing additional round trips and buffer modifications during transactions, or correctly, during DML operations.

Anyway, sometimes it’s just the best way to solve a problem, so this is how to create a Materialized View in Oracle Database 12.1 and after, with Fast Refresh on commit including debugging with DBMS_MVIEW.EXPLAIN_MVIEW.

Create a Materialized View with Fast Refresh on commit

Let’s drop the Base View first

drop view MYVIEW;

Define a Base View (not mandatory, direct queries would also work, even better)

create view MYVIEW as 
select a.id,
       b.id,
       c.id,
       a.rowid, -- ROWID is necessary if table has not Primary Key Constraint defined
       b.rowid,
       c.rowid
 from MYTABLE_A a, MYTABLE_B b, MYTABLE_C c
 where a.id=b.aid
       and b.id=c.bid
;

Drop MV logs

drop materialized view log on MYTABLE_A; 
drop materialized view log on MYTABLE_B; 
drop materialized view log on MYTABLE_C;

Create MV logs on base table. If one has no Primary Key, we have to use WITH ROWID

create materialized view log on MYTABLE_A with rowid; 
create materialized view log on MYTABLE_B with rowid; 
create materialized view log on MYTABLE_Cwith rowid;

Define MV (would also work with a direct query on base tables)

create materialized view MYMV refresh fast on commit
 as select * from MYVIEW;

Ugly test:

select * from MYVIEW;

Refresh

It’s now quite pointless, except we did not set ON COMMIT at creation time, should be fast in this case, anyway

begin
 DBMS_MVIEW.REFRESH ( 'MYMV', 'F' );
end;
/

Debugging Errors at Creation or during later Refresh

Create MV_CAPABILITIES_TABLE

We need a table to buffer results of the EXPLAIN step below. Its DDL was borrowed from Oracle Doc 12.1

CREATE TABLE MV_CAPABILITIES_TABLE 
(STATEMENT_ID VARCHAR(30), -- Client-supplied unique statement identifier
 MVOWNER VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
 MVNAME VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
 CAPABILITY_NAME VARCHAR(30), -- A descriptive name of the particular
 -- capability:
 -- REWRITE
 -- Can do at least full text match
 -- rewrite
 -- REWRITE_PARTIAL_TEXT_MATCH
 -- Can do at least full and partial
 -- text match rewrite
 -- REWRITE_GENERAL
 -- Can do all forms of rewrite
 -- REFRESH
 -- Can do at least complete refresh
 -- REFRESH_FROM_LOG_AFTER_INSERT
 -- Can do fast refresh from an mv log
 -- or change capture table at least
 -- when update operations are
 -- restricted to INSERT
 -- REFRESH_FROM_LOG_AFTER_ANY
 -- can do fast refresh from an mv log
 -- or change capture table after any
 -- combination of updates
 -- PCT
 -- Can do Enhanced Update Tracking on
 -- the table named in the RELATED_NAME
 -- column. EUT is needed for fast
 -- refresh after partitioned
 -- maintenance operations on the table
 -- named in the RELATED_NAME column
 -- and to do non-stale tolerated
 -- rewrite when the mv is partially
 -- stale with respect to the table
 -- named in the RELATED_NAME column.
 -- EUT can also sometimes enable fast
 -- refresh of updates to the table
 -- named in the RELATED_NAME column
 -- when fast refresh from an mv log
 -- or change capture table is not
 -- possible.
 -- See Table 5-6
 POSSIBLE CHARACTER(1), -- T = capability is possible
 -- F = capability is not possible
 RELATED_TEXT VARCHAR(2000), -- Owner.table.column, alias name, and so on
 -- related to this message. The specific 
 -- meaning of this column depends on the 
 -- NSGNO column. See the documentation for
 -- DBMS_MVIEW.EXPLAIN_MVIEW() for details.
 RELATED_NUM NUMBER, -- When there is a numeric value 
 -- associated with a row, it goes here.
 MSGNO INTEGER, -- When available, QSM message # explaining
 -- why disabled or more details when
 -- enabled.
 MSGTXT VARCHAR(2000), -- Text associated with MSGNO.
 SEQ NUMBER); -- Useful in ORDER BY clause when 
 -- selecting from this table.

Debugging

The Debug operation fills its data into the table created above

begin
 DBMS_MVIEW.EXPLAIN_MVIEW ('
 create materialized view MYMV 
 refresh fast on commit
 as select * from MYVIEW ');
end;
/

We also can execute the EXPLAIN with an existing MV, given, we were able to create it.

begin
 DBMS_MVIEW.EXPLAIN_MVIEW ('MYVIEW');
end;
/

Showing the results

Using MV_CAPABILITIES_TABLE

SELECT *
FROM MV_CAPABILITIES_TABLE
where MVNAME = 'MYMV'
ORDER BY seq;

Above results are widely self-explanatory, but some are explained in the docs or here:

Rob van Wijk’s nice blog:
http://rwijk.blogspot.de/2009/09/fast-refreshable-materialized-view_13.html

Oracle Documentation link:
https://docs.oracle.com/database/121/DWHSG/basicmv.htm#DWHSG8219

Archives

Topics

About

Martin Klier