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;
select * from MYVIEW;
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
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.
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
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:
Oracle Documentation link: