Till 9i, if you did a COMPLETE Refresh of a Single Materialized View, Oracle would do a TRUNCATE followed by an INSERT.
If you did a COMPLETE Refresh of a *group* of Materialized Views, Oracle would execute DELETE and INSERT so that all the MVs would be consistent to the same point in time. Thus, if one of the MVs failed to refresh (e.g. the SELECT on the Source Table failed or the INSERT failed, it would be able to do a ROLLBACK of all the MVs to revert them to the status (i.e. all rows that were present) as of the time before the Refresh began. This would also allow all MVs to be available for queries with the rows as of before the Refresh began, even as the Refresh was running (because the Refresh of the multiple MVs was a single transaction).
In 10g, the behaviour for a *group* of Materialized Views remained the same. However, for a single MV, the default was now to do a DELETE and INSERT as well. This would allow the MV to be queryable as well while the Refresh was running.
This change came as a surprise to many customers (including me at a site where I was managing multiple single MVs) !
This change meant that the single MV took longer to run (because DELETEing all the rows takes a long time !) and required much more Undo and Redo space !!
Here's a demonstration in
19c (as in the previous posts in this series) :
First, I start with a new, larger, Source Table and then build two MVs on it :
SQL> create table source_table_2
2 as select *
3 from dba_objects
4 where object_id is not null
5 /
Table created.
SQL> alter table source_table_2
2 add constraint source_table_2_pk
3 primary key (object_id)
4 /
Table altered.
SQL> select count(*)
2 from source_table_2
3 /
COUNT(*)
----------
72366
SQL>
SQL> create materialized view new_mv_2_1
2 as select object_id, owner, object_name, object_type
3 from source_table_2
4 /
Materialized view created.
SQL> create materialized view new_mv_2_2
2 as select object_id, owner, object_name, object_type
3 from source_table_2
4 /
Materialized view created.
SQL>
SQL> select mview_name, refresh_mode, refresh_method, last_refresh_type, fast_refreshable
2 from user_mviews
3 where mview_name like 'NEW_MV%'
4 order by 1
5 /
MVIEW_NAME REFRESH_M REFRESH_ LAST_REF FAST_REFRESHABLE
---------------- --------- -------- -------- ------------------
NEW_MV_2_1 DEMAND FORCE COMPLETE DIRLOAD_DML
NEW_MV_2_2 DEMAND FORCE COMPLETE DIRLOAD_DML
SQL>
Note that it *IS* possible to have two Materialized Views with exactly the same QUERY co-existing. They may have different REFRESH_METHODs (here both are the same) and/or may have different frequencies of Refresh calls when the REFRESH_MODE is 'DEMAND'
Note also that I did not specify any "refresh
on demand" clause so both defaulted to FORCE and DEMAND.
(Question 1 : Why might I have two MVs with the same QUERY and the same REFRESH_METHOD but different frequency or different times when the Refresh is called ?)
(
Question 2 : What is DIRLOAD_DML ?)
Now, let me issue two different COMPLETE Refresh calls and trace them.
SQL> execute dbms_mview.refresh('NEW_MV_2_1','C');
SQL> execute dbms_mview.refresh('NEW_MV_2_2','C',atomic_refresh=>FALSE); -- from a different session
Now, I look at the trace files.
For the NEW_MV_2_1 (where ATOMIC_REFRESH defaulted to TRUE), I see :
/* MV_REFRESH (DEL) */ delete from "HEMANT"."NEW_MV_2_1"
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "HEMANT"."NEW_MV_2_1"("OBJECT_ID","OWNER","OBJECT_NAME","OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER","SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM "SOURCE_TABLE_2" "SOURCE_TABLE_2"
And for the NEW_MV_2_2 (where ATOMIC_REFRESH was set to FALSE), I see :
LOCK TABLE "HEMANT"."NEW_MV_2_2" IN EXCLUSIVE MODE NOWAIT
/* MV_REFRESH (DEL) */ truncate table "HEMANT"."NEW_MV_2_2" purge snapshot log
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "HEMANT"."NEW_MV_2_2"("OBJECT_ID","OWNER","OBJECT_NAME","OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER","SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM "SOURCE_TABLE_2" "SOURCE_TABLE_2"
So, the default ATOMIC_REFRESH=TRUE caused a DELETE followed by an INSERT while the ATOMIC_REFRESH=FALSE caused a DELETE followed by an INSERT APPEND (a Direct Path Insert). The second method is much faster.
More information from a tkprof for the
NEW_MV_2_1 (ATOMIC_REFRESH=TRUE) is :
INSERT INTO "HEMANT"."NEW_MV_2_1"("OBJECT_ID","OWNER","OBJECT_NAME",
"OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER",
"SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM
"SOURCE_TABLE_2" "SOURCE_TABLE_2"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 66 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.01 0 66 0 0
delete from "HEMANT"."NEW_MV_2_1"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.47 1.77 151 173 224377 72366
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.47 1.77 151 173 224377 72366
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE NEW_MV_2_1 (cr=178 pr=151 pw=0 time=1783942 us starts=1)
72366 72366 72366 INDEX FAST FULL SCAN SYS_C_SNAP$_82SOURCE_TABLE_2_PK (cr=157 pr=150 pw=0 time=54982 us starts=1 cost=42 size=361830 card=72366)(object id 73111)
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "HEMANT"."NEW_MV_2_1"("OBJECT_ID",
"OWNER","OBJECT_NAME","OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID",
"SOURCE_TABLE_2"."OWNER","SOURCE_TABLE_2"."OBJECT_NAME",
"SOURCE_TABLE_2"."OBJECT_TYPE" FROM "SOURCE_TABLE_2" "SOURCE_TABLE_2"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 4 0
Execute 1 0.71 0.71 0 2166 152128 72366
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.71 0.71 0 2166 152132 72366
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL NEW_MV_2_1 (cr=2257 pr=0 pw=0 time=723103 us starts=1)
72366 72366 72366 TABLE ACCESS FULL SOURCE_TABLE_2 (cr=1410 pr=0 pw=0 time=30476 us starts=1 cost=392 size=3980130 card=72366)
Note that the first INSERT was only Parsed but *not* Executed.
While that for
NEW_MV_2_2 (ATOMIC_REFRESH=FALSE) shows :
LOCK TABLE "HEMANT"."NEW_MV_2_2" IN EXCLUSIVE MODE NOWAIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
truncate table "HEMANT"."NEW_MV_2_2" purge snapshot log
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.06 0.56 13 15 511 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 0.57 13 15 512 0
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO
"HEMANT"."NEW_MV_2_2"("OBJECT_ID","OWNER","OBJECT_NAME","OBJECT_TYPE")
SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER",
"SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM
"SOURCE_TABLE_2" "SOURCE_TABLE_2"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.09 0 43 0 0
Execute 1 0.22 0.56 3 1487 1121 72366
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.24 0.65 3 1530 1121 72366
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT NEW_MV_2_2 (cr=3688 pr=7 pw=586 time=953367 us starts=1)
72366 72366 72366 OPTIMIZER STATISTICS GATHERING (cr=3337 pr=0 pw=0 time=142500 us starts=1 cost=392 size=3980130 card=72366)
72366 72366 72366 TABLE ACCESS FULL SOURCE_TABLE_2 (cr=1410 pr=0 pw=0 time=40841 us starts=1 cost=392 size=3980130 card=72366)
ALTER INDEX "HEMANT"."SYS_C_SNAP$_83SOURCE_TABLE_2_PK" REBUILD NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 1 1 0
Execute 1 0.11 0.48 586 626 680 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.11 0.56 586 627 681 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD UNIQUE SYS_C_SNAP$_83SOURCE_TABLE_2_PK (cr=632 pr=586 pw=150 time=392351 us starts=1)(object id 0)
72366 72366 72366 SORT CREATE INDEX (cr=590 pr=586 pw=0 time=148023 us starts=1)
72366 72366 72366 MAT_VIEW ACCESS FULL NEW_MV_2_2 (cr=590 pr=586 pw=0 time=86149 us starts=1 cost=166 size=361830 card=72366)
So, the ATOMIC_REFRESH=FALSE caused
a. TRUNCATE
b. INSERT APPEND (i.e. Direct Path Insert, minimizing Undo and reducing Redo)
c. REBUILD INDEX
I am not comparing Execution Time for the two Refresh's. I would rather that you focus on the fact that the DELETE (in ATOMIC_REFRESH=TRUE) can be very expensive (think Undo and Redo) when it has delete, say, millions of rows. Also, that the INSERT is a regular operation that also causes Undo and Redo to be generated.
ATOMIC_REFRESH=FALSE makes a significant difference to the Undo and Redo generation and will be faster for large Materialized Views.
What is the downside of ATOMIC_REFRESH=FALSE ? Firstly, the MV will not present any rows to a query that executes against it while the Refresh is in progress. Secondly, if the Refresh fails, the MV is left in a Truncated state (without rows) until another Refresh is executed.
The ATOMIC_REFRESH=TRUE avoids these two pitfalls, at the expense of resources (Undo and Redo) and time to refresh.
For more information, see Oracle Support Document #553464.1
