Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Andre.

Asked: December 31, 2000 - 7:42 pm UTC

Last updated: July 26, 2007 - 5:46 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom, and Happy New Year !

I need to setup a policy to refresh a materialized view. Some points were not clear to me.

Questions:

1) Since I plan to have summaries from tables which are loaded at uncertain times -- and I need to have them updated. So I cannot use START WITH. It seems to me there are 2 options:
a) ON DEMAND (default) by scheduling a manual refresh using DBMS_MVIEW after the load.
b) ON COMMIT, since SQL*LOADER itself will commit the data.
Apparently, ON COMMIT is better because I don't have to schedule a manual refersh, which involves more work. Am I correct, or are there other implications ?

2) If I create a MV with a COMPLETE, FAST, FORCE or NEVER method, will the manual refresh use the respective method to refresh the MV ? Or will it, for example, always refresh the MV entirely (something like the COMPLETE method) ?

3) I understand the COMPLETE and FAST methods. But -- according to Oracle -- FORCE would "apply fast refresh if possible; otherwise, apply complete refresh". What does he mean by "if possible" ? Would you please comment on the FORCE method ?

4) The NEVER method is said to never refresh the MV in any way. Does that also apply to the refresh procedures of DBMS_MVIEW ?

5) Is it mandatory that I create a materialized view log whenever I use the fast refresh method ?

Thanks !

and Tom said...

1) you can use start with and next if you like to schedule a refresh to occur on some scheduled time (eg: start right now and refresh every night at midnight). You can still do an on demand based refresh as well -- by using the supplied refresh routines in the plsql packages.

On commit will add a certain amount of overhead to the load process so, if the load is "large", you might be best off doing the load and then a complete refresh of the materialized views (rather then applying all of the changes, it may be faster just to rebuild the entire mv).

So, on commit might mean a little less administrative overhead but a little more runtime processing horsepower. If you are loading large amounts of data, the overhead might be enough to convince you to schedule a complete refresh "post" load.

2) the refresh will use by default whatever the MV was created with (complete/fast). you can force it to use one or the other at runtime as well using the dbms_mview.refresh procedure with method set to one of C, F, or ?

3) if we can do a fast (the snapshot log exists, it has not been purged, the mv is a candidate for a fast refresh, and so on) we do a fast. If we cannot, we do a complete. A force just ensures that one way or the other, the MV is refreshed.

4) NEVER REFRESH prevents the materialized view from being refreshed with any Oracle refresh mechanism or procedure. If you issue a REFRESH statement on the materialized view, Oracle returns an error.

5) For a fast refresh, yes we need a snapshot log as this is the list of all changes to the base table(s) made since the last fast refresh.



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Force refresh :: if possible

Brendan, January 29, 2004 - 7:49 am UTC

In response to the answer to question 3 above ("if possible"). I understand that the snapshot log needs to exist for the fast refresh to work.
My question is to do with the use of the word "purge".

When you say purged(an act of removing by cleansing), do you simply mean that the log is empty?

eg.
When an inital refresh is performed on a simple MV, you would expect that a force refresh would perform a complete refresh. The next time that a force refresh is performed on this MV you would expect a fast refresh to occur.

Is this assumption correct?

A thing that I am struggling to find information on is the "if possible" scenarios. I understand that complex queries, MVs with no log and MVs with purged logs cannot use a fast refresh, but, is there a reference giving a list of all other situations where a fast refresh will not be performed over a complete when a force refresh is attempted?

Tom Kyte
January 29, 2004 - 8:13 am UTC

when I say purged, i mean purge as in the dbms_mview api calls -- to "cleanse" the log, yes.


an initial refresh may or may not do a complete. I would not expect it to, no. I just created an mv with refresh fast. I then refresh later -- i would expect it to use the logs to fast refresh it (incrementally refresh it, i'm not fond of the word "fast" there since it could be "really slow refresh" just as easily as "fast")

If a mv is fast refreshable (we need not concern non-fast refreshable mv's at all, they'll never be incrementally maintained), then it'll fast refresh as long as the mv log table has the sufficient information in it. that is, we didn't drop and rebuild the log, we didn't do something to the parent table that didn't maintain the log (admin bulky operations), we didn't purge the mv log.

Large number of updates on MV source table

Sami, March 18, 2004 - 5:50 pm UTC

Dear Tom,

Snapshot TAB1_MV is created in DB1 based on the table TAB1 located in remote db DB2.

Refresh time : 5 Min
Refresh Mode : FAST
Updateable : No
Segment Size : 4000 MB
IM index rebuild is happening thru DBMS_JOB.


The table and snapshot have BLOB columns and intermedia index has been created on BLOB column.
The table TAB1 at DB2 will get increased by 10% because of stress test by running script SCRIPT1.sql.



1) We cannot run SCRIPT1.sql because TAB1_MV is Read-Only snapshot.

2) If we keep the replication on, it will break because of the large number of updates in Remote site (DB2).

Based on the given scenario what is the best way to go?



Tom Kyte
March 18, 2004 - 6:12 pm UTC

1) huh? you run script1 in DB-2 against TAB1. it'll change tab1. the changes will propagate to DB-1, TAB1_MV

so, not sure why "you cannot run it"

2) why would it "break"? I sure hope it doesn't "break".

Turning off MV Log

A reader, July 25, 2007 - 4:18 pm UTC

Hi Tom and thanks for the awesome service you provide the Oracle community.

Please consider: MV of a Base table with a MV Log so we can do incremental refreshes. The base table for the MV is usually incrementally changed; however, occasionally the Base table is trunc'd and completely rebuilt. Is there any way to avoid the inserts to the MV Log without dropping it? The idea would be to somehow trunc the Base table and MV Log, load the Base table, and then do a complete/full refresh of the MV. Regards, Rick
Tom Kyte
July 26, 2007 - 5:46 pm UTC

can you load the base table using APPEND (direct path)? that'll avoid massive log entries.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library