Start with 12c a new mechanism of refreshing mviews
available, this refresh creates one or more outside tables, executes the
refresh statements on the outside tables, and then switches the materialized
view or affected materialized view partitions with the outside tables. An
out-of-place refresh achieves high availability during refresh, especially when
refresh statements take a long time to finish.
rajesh@PDB1> create table t as select * from all_objects a;
Table created.
Elapsed: 00:00:06.14
rajesh@PDB1>
rajesh@PDB1> create materialized view t_mv
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select owner, count(*)
7 from t
8 group by owner ;
Materialized view created.
Elapsed: 00:00:00.54
rajesh@PDB1>
rajesh@PDB1> insert into t
2 select * from all_objects a
3 where rownum <=5;
5 rows created.
Elapsed: 00:00:00.88
rajesh@PDB1> commit;
Commit complete.
Elapsed: 00:00:00.01
rajesh@PDB1>
rajesh@PDB1> begin
2 dbms_mview.refresh('T_MV','C',
3 atomic_refresh=>false,
4 out_of_place=>true);
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:23.11
rajesh@PDB1>
With these settings the session will create a new table with a name of
the form RV$xxxxxx where xxxxxx is the hexadecimal version of the new object
id, insert the new data into that table (though not using the /*+ append */
hint), create the indexes on that table (again with names like RV$xxxxxx -
where xxxxxx is the index's object_id). Once the new data has been indexed Oracle
will do some name-switching in the data dictionary (shades of exchange
partition) to make the new version of the materialized view visible. A quirky
detail of the process is that the initial create of the new table and the final
drop of the old table don't show up in the trace file, although the commands to
drop and create indexes do appear. (The original table, though it's dropped
after the name switching, is not purged from the recyclebin.) The impact on
undo and redo generation is significant
And the trace file shows me this
********************************************************************************
SQL ID: a2myrwrykjmd2 Plan Hash: 0
drop table "RAJESH"."RV$16D83"
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 2 0.00
0.00 0 0 0 0
Execute 2 0.06
0.18 0 1 2 0
Fetch 0 0.00
0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 4 0.06
0.18 0 1 2 0
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 103
(recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
reliable message 1 0.00 0.00
enq: RO - fast object
reuse 1 0.00 0.00
********************************************************************************
SQL ID: 7vh7psa1x25g2 Plan Hash: 1064870033
CRE
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 1 0.00
0.06 0 27 0 0
Execute 1 0.03
0.02 0 33 3 0
Fetch 0 0.00
0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 2 0.03
0.09 0 60 3 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)
Row Source Operation
---------- ---------- ----------
---------------------------------------------------
0 0 0
LOAD AS SELECT (cr=0 pr=0 pw=0
time=282 us)
0 0 0
OPTIMIZER STATISTICS GATHERING
(cr=0 pr=0 pw=0 time=193 us)
0 0 0
HASH GROUP BY (cr=0 pr=0 pw=0 time=135 us)
0 0 0
FILTER (cr=0 pr=0 pw=0 time=6 us)
0 0 0
TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us cost=418 size=536076
card=89346)
********************************************************************************
SQL ID: a3xs164hn3m3t Plan Hash: 47235625
INSERT INTO "RAJESH"."RV$16D83"
("OWNER","COUNT(*)") SELECT
"T"."OWNER",
COUNT(*) FROM
"T" "T" GROUP BY "T"."OWNER"
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 1 0.01
0.00 0 0 0 0
Execute 1 0.04
0.06 0 1512 42 26
Fetch 0
0.00 0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 2 0.06
0.06 0 1512 42 26
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103
(recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)
Row Source Operation
---------- ---------- ----------
---------------------------------------------------
0 0 0
LOAD TABLE CONVENTIONAL RV$16D83
(cr=1511 pr=0 pw=0 time=53720 us)
26 26 26
HASH GROUP BY (cr=1509 pr=0 pw=0 time=36496 us cost=420 size=156 card=26)
89351 89351
89351 TABLE ACCESS FULL T
(cr=1509 pr=0 pw=0 time=13055 us cost=418 size=536076 card=89346)
********************************************************************************
/* MV_REFRESH (CIDX) */ CREATE UNIQUE INDEX
"RAJESH"."RV$16D84" ON
"RAJESH"."RV$16D83" (SYS_OP_MAP_NONNULL("OWNER"))
PCTFREE 10
INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE
"DATA_12C"
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 1 0.00
0.00 0 1 1 0
Execute 1 0.01
0.03 0 9 45 0
Fetch 0 0.00
0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 2 0.01
0.03 0 10 46 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103
(recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)
Row Source Operation
---------- ---------- ----------
---------------------------------------------------
1 1 1
INDEX BUILD UNIQUE RV$16D84 (cr=12 pr=0 pw=0 time=23852 us)(object id 0)
26 26 26
SORT CREATE INDEX (cr=7 pr=0 pw=0 time=388 us)
26 26 26
TABLE ACCESS FULL RV$16D83 (cr=7 pr=0 pw=0 time=143 us cost=2 size=66
card=1)
********************************************************************************
SQL ID: 24jzz1qu842c0 Plan Hash: 0
LOCK TABLE FOR
INDEX "RAJESH"."RV$16D84" 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
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103
(recursive depth: 2)
********************************************************************************
SQL ID: d41ntutwcktng Plan Hash: 0
DROP INDEX RAJESH.RV$16D84
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 1 0.00
0.00 0 0 1 0
Execute 1 0.07
0.16 1 1 9 0
Fetch 0 0.00
0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 2 0.07
0.16 1 1 10 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103
(recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait
Total Waited
---------------------------------------- Waited
---------- ------------
reliable message 2 0.00 0.00
enq: RO - fast object
reuse 1 0.00 0.00
db file sequential
read 1 0.00 0.00
enq: CR - block range
reuse ckpt 1 0.00 0.00
********************************************************************************
declare
stmt
varchar2(200);
cnt number;
BEGIN
if
sys.dbms_standard.dictionary_obj_type = 'USER' THEN
stmt :=
'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
' WHERE
SDO_OWNER = :owner ';
EXECUTE
IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name;
stmt :=
'DELETE FROM SDO_MAPS_TABLE ' ||
' WHERE
SDO_OWNER = :owner ';
EXECUTE
IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name;
stmt :=
'DELETE FROM SDO_CACHED_MAPS_TABLE ' ||
' WHERE
SDO_OWNER = :owner ';
EXECUTE
IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name;
stmt :=
'DELETE FROM SDO_STYLES_TABLE ' ||
' WHERE
SDO_OWNER = :owner ';
EXECUTE
IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name;
stmt :=
'DELETE FROM SDO_THEMES_TABLE ' ||
' WHERE
SDO_OWNER = :owner ';
EXECUTE
IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name;
stmt :=
'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
' WHERE
SDO_OWNER = :owner ';
EXECUTE
IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name;
stmt :=
'DELETE FROM SDO_TOPO_METADATA_TABLE ' ||
' WHERE
SDO_OWNER = :owner ';
EXECUTE
IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name;
stmt :=
'DELETE FROM SDO_ANNOTATION_TEXT_METADATA ' ||
' WHERE
F_TABLE_SCHEMA = :owner ';
EXECUTE IMMEDIATE stmt USING
sys.dbms_standard.dictionary_obj_name;
end if;
end;
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 2 0.01
0.08 0 0 0 0
Execute 2 0.00
0.00 0 0 0 2
Fetch 0 0.00
0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 4 0.01
0.08 0 0 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79
(recursive depth: 2)
********************************************************************************
DECLARE
stmt VARCHAR2(256);
BEGIN
IF sys.dbms_standard.dictionary_obj_type =
'USER' THEN
stmt :=
'DELETE FROM SDO_NETWORK_METADATA_TABLE WHERE SDO_OWNER = :name';
EXECUTE
IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name);
stmt :=
'DELETE FROM SDO_NETWORK_CONSTRAINTS WHERE SDO_OWNER = :name';
EXECUTE
IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name);
stmt := 'DELETE
FROM SDO_NETWORK_LOCKS_WM WHERE SDO_OWNER = :name';
EXECUTE
IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name);
stmt :=
'DELETE FROM SDO_NETWORK_USER_DATA WHERE SDO_OWNER = :name';
EXECUTE
IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name);
--lrg-3417773
stmt :=
'DELETE FROM SDO_NETWORK_HISTORIES WHERE OWNER = :name';
EXECUTE
IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name);
stmt :=
'DELETE FROM SDO_NETWORK_TIMESTAMPS WHERE OWNER = :name';
EXECUTE
IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name);
stmt :=
'DELETE FROM SDO_NETWORK_FEATURE WHERE SDO_OWNER = :name';
EXECUTE
IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name);
END IF;
END ;
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 2 0.01
0.01 0 0 0 0
Execute 2 0.00
0.00 0 0 0 2
Fetch 0 0.00
0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 4 0.01
0.01 0 0 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79
(recursive depth: 2)
********************************************************************************
After refresh completed, dropped segments still exists in recyclebin.
rajesh@PDB1> select object_name,original_name,operation,type,
2 related,base_object,purge_object
3 from user_recyclebin ;
OBJECT_NAM ORIGINAL_N OPERATION
TYPE RELATED BASE_OBJECT
PURGE_OBJECT
---------- ---------- ---------- ---------- ----------
----------- ------------
BIN$5OO3BQ RV$16D83
DROP TABLE 93571 93571 93571
1 row selected.
Elapsed: 00:00:00.18
rajesh@PDB1> select to_number('16D83','xxxxx') from dual ;
TO_NUMBER('16D83','XXXXX')
--------------------------
93571
1 row selected.
Elapsed: 00:00:00.00
rajesh@PDB1>
