Archive for November, 2017

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
;

Read more…


By Martin Klier in Oracle,Performance  .::. (Add your comment)


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.