We use materialized views in Oracle to copy data from our production database to our data warehouse for Cognos reporting. These materialized views all refresh over night. This works great, but over time, some of the tables have become quite large. One troublesome table is over 12 million rows and was taking an hour to refresh each night.
The problem with our setup is that it’s pointless to delete 12 million rows, then reinsert all 12 million just to refresh the production changes that affected maybe 300 records in any given day. This generates a LOT of extra CPU load, network traffic, and most importantly, unnecessary disk io. On top of that, Oracle starts kicking out redo logs for everything deleted! High performance is critical, especially when you’ve got 200 of these materialized views all refreshing at the same time, with 6-10 concurrently.
We were able to solve this problem using “fast” refreshes. It works by keeping an audit log of new and changed records in the production database. When the materialized view refreshes on the reporting database, only the changed records are deleted, then both new and changed are inserted.
In your production database, begin by attaching a materialized view log to the tables you are refreshing. We always recommend placing these logs in a tablespace of their own.
create materialized view log on RELATEMGR.REL_INTERACTIONLOG tablespace MVIEW_LOGS_TBS with primary key including new values;
“tablespace” – The tablespace where your materialized view log will be stored
“with primary key” – record the primary key in the materialized view log
“including new values” – new records will be added to the materialized view log
Next, create the materialized view in the secondary database. We are using a database link called “PROD” in this example to move the data from one instance to another.
create materialized view CUSTOM.BRM_INTERACTIONLOG_MV refresh force with primary key as select * from RELATEMGR.REL_INTERACTIONLOG@PROD;
“refresh force” – choose the best refresh method. If a “fast” refresh cannot be done, a “complete” refresh will be performed.
“with primary key” – create the primary key in the materialized view the same as the production table
Once that’s done, you can change a few records on the production database and issue a refresh. You won’t notice much of a difference with only a few records in the table. Once you get to tens of thousands, you’ll notice the refresh only takes a few milliseconds.