Under the hood – MV complete refresh

If MV is refresh on demand, Oracle does truncate/insert(append).
If MV is refresh on commit, Oralce does delete/insert.
That’s why MV is usually not for OLTP environment.
In OLTP environment and your MV is not fast refreshable, if you choose refresh on demand, you have a race-condition where the MV will be empty for a while(may be just some milliseconds, but still a while in an OLTP envrionment). If you choose refresh on commit, your transaction will take longer to complete and also more undo/redo for delete and conventional insert.
In DSS or DW, typically you refresh the MV on demand in the batch job at night when no one will notice or care.

Now the question is what to do with real-time DW?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: