Materialized View 1

[jaxtell]

I have a question about refreshing materialized views. In my environment, 95% of the materialized views are in a reporting instance and are only refreshed nightly, when no one is using the system. I now have a need to create a materialized view in our transactional environment, and it will need to be refreshed regularly (possibly as often as every 5 minutes). My question is what happens to queries/views referencing this materialized view while its being refreshed? I'm guessing the old data is available until all the new data is available, but I'd like to be sure. I expect it to refresh quite quickly, but I'd like to know any possible side effects. Thanks.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[SantaMufasa]

Our "expert" opinions about materialized views are immaterial if they differ from an actual test.

So, my suggestion is to simply:[ul][li]Identify data in the existing version of the materialized view that will change subsequent to a refresh,[/li][li]Prepare a query that returns the data from that row.[/li][li]Initiate a refresh of the MV[/li][li]Run your query[/li][li]Post your results here so we can all learn from your findings. [/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[karluk]

Doing an actual test in your own database, as SantaMufasa suggests, is a good idea, since this appears to be a fairly complicated subject. The correct answer (as often happens when talking to DBAs) is, "It depends".

The relevant parameter of dbms_mview.refresh is "atomic_refresh" and whether it is set to true or false. The default value is true, but this alone is not enough to guarantee that a refresh really is atomic. If you are running on Oracle 9.2 or earlier, there is an unpublished bug (3168840) which caused complete refreshes of even a single materialized view to be nonatomic. Basically Oracle implemented complete refreshes by truncating the materialized view first and then inserting. The truncate caused a commit, so the materialized view appears to other users to be empty during the refresh. This has been corrected in 10g by replacing the truncate with a delete. That fixes the bug, but also causes complete refreshes to run slower in Oracle 10g than in earlier releases.

To the best of my knowledge there are no similar problems with fast refreshes, regardless of the Oracle release level.

So, in Oracle 10g, you should see read consistent results during the refresh if you are (1) doing a fast refresh or (2) doing a complete refresh with "atomic_refresh" set to true. The only situation that would still result in inconsistent results is doing a complete refresh with "atomic_refresh" set to false. Only in this situation has Oracle retained the old behavior of doing a truncate followed by an insert. This allows users the option of getting the refreshes to execute more quickly at the cost of read consistency.

 

[SantaMufasa]

Now that's an expert opinion that you can trust!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”