materialized view Refresh

About materialized view Refresh
=============================

As I find, Materialized View create and ("atomic_refresh => FALSE") refresh is faster and more logical Then create table ,truncate and direct insert.

about materialized view works
=================
1. create a MV with "complete refresh on demand" (means it refresh when we want, not automatic)
2. when we refresh that MV (ON DEMAND REFRESH MODE) then
• ON DEMAND – refreshes are initiated manually using one of the procedures in the DBMS_MVIEW package
• Can be used with all types of materialized views
• Manual Refresh Procedures
• DBMS_MVIEW.REFRESH(, )

• When refresh_option use ("atomic_refresh => TRUE") its means DELETE operation is used to empty the table.
( in this time if job is broken then rollback the transaction and MV remain useable)
• When refresh_option use ("atomic_refresh => FALSE") its means TRUNCATE operation is used to empty the table.
( in this time if job is broken then MV don't remain useable state before another refresh)

• AND we can reduce the LOG generation by creating MV with nologging mode.

Ultimately
================
Oracle performs the following operations when refreshing a materialized view. In the case of a complete refresh (using dbms_mview.refresh)
1. sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
2. The materialized base view is truncated/delete.
3. All rows selected from the master table are inserted into the snapshot base table.
4. sys.slog$ is updated to reflect the time of the refresh.


• N.B :- I have attached a “trace file .txt” file. Just search in this file with MV_STTRAILB_038 and find the prob.