I am aware that fast refresh needs a mv log.
But is there a way a log can be avoided.
No, for the reason that you've just said that you're aware of.
Fast refreshing means that Oracle only applies changes to the MV that have arisen since the last time it was refreshed. The only way it can know what those changes are is to log them somehow. That's why you need a MV log.
Thanks for the reply.
Please let me know the effect of log on the performance of mv.
Complete refresh will surely take time than a fast refresh.So was thinking to create a fast refresh mview but was unsure if a log can be avoided (reason performance)
And also while creating a mv with complete refresh i am not specifying log/nolog so when does the nologging comes into picture.
> Complete refresh will surely take time than a fast refresh.
Well...maybe. It would depend on the activity, number of changes, etc.
On the other hand if you do a complete refresh with atomic = false, that will do a truncate and an insert/append. That might be pretty fast.
Only testing will tell you for sure.
But first you should see if your mv is fast refreshable. Not all are.
The procedure dbms_mview.explain_mview can tell you if an mview is fast refreshable (among other things).
Please let me know if I can create a materialized view fast refresh on commit with nologging.
Yes - you can. The NOLOGGING refers to the REDO logging and applies when the MV is first created or when a complete, NON-ATOMIC refresh is performed.
I am aware that fast refresh needs a mv log.But is there a way a log can be avoided.
I am working on 10g.
You are confusing some responders with your use of the word 'log'.
Fast refresh requires an MV log and will always generate REDO log information.
If the MV is defined as NOLOGGING then you can avoid REDO logging when a COMPLETE NON-ATOMIC refresh is performed.
See this AskTom blog for an explanation by Oracle ACE and author Tom Kyte:
yeah actually I am bit confused about the logging/nologging and mv log.Could you please correct if I am going wrong
mv log records the dml changes made to the table and is used for refreshing the data in a fast refresh scenario.
as far as I understood during complete refresh a delete +insert is happening so the redo log information(of the delete) is stored in the log (logging clause used during create mv).
In order to avoid the log to be created we can use nologging .
Atomic refresh will do a truncate + insert so no log is generated.
I will try a sample to check the working.
Thanks in advance.