Complete refresh also has to be used
Pratap, January 10, 2008 - 4:18 am UTC
In our case, where the base table is partitioned, we had to use the complete refresh option explicitly to force a NOLOGGING and COMPRESS. The MV was not created stating any refresh option. Here is a test case -
CREATE TABLE test_base
(
CNTRY_ID VARCHAR2(2 CHAR)
)
PARTITION BY LIST (CNTRY_ID)
(
PARTITION RDM_SLSLINE_P_DEU VALUES ('DE')
LOGGING
NOCOMPRESS,
PARTITION RDM_SLSLINE_P_ITA VALUES ('IT')
LOGGING
NOCOMPRESS,
PARTITION RDM_SLSLINE_P_OTHERS VALUES (DEFAULT)
LOGGING
NOCOMPRESS
);
ALTER TABLE test_base ADD PRIMARY KEY (cntry_id);
INSERT INTO test_base VALUES ( 'IT' );
CREATE MATERIALIZED VIEW mv_test
NOLOGGING COMPRESS BUILD DEFERRED
AS
SELECT *
FROM test_base;
BEGIN
dbms_mview.REFRESH('mv_test');
END;
SELECT last_refresh_type
FROM user_mviews
WHERE mview_name = 'MV_TEST';
Now for the first time it does a complete refresh. Now if I refresh the MV again, it does a FAST_PCT. In this case the compression does not take place. If I explicitly force a complete refresh then the compression takes place.
An unrelated question -> When there is no MV log, how did the MV do a FAST_PCT and not a complete refresh.
Regards,
Pratap
January 10, 2008 - 2:21 pm UTC
ops$tkyte%ORA10GR2> BEGIN
2 dbms_mview.REFRESH('mv_test');
3 END;
4 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> SELECT last_refresh_type
2 FROM user_mviews
3 WHERE mview_name = 'MV_TEST';
LAST_REF
--------
COMPLETE
I don't see what you say - cut and pastes are ALWAYS preferred (eg: your script would not have refreshed anything had you run it, missing slash - so I don't know what you've done)
Correction in above post
Pratap, January 10, 2008 - 7:21 am UTC
The refresh method used was -
BEGIN
dbms_mview.REFRESH ( LIST => 'mv_test', METHOD => 'c', atomic_refresh => FALSE );
END;
January 10, 2008 - 2:36 pm UTC
ops$tkyte%ORA10GR2> BEGIN
2 dbms_mview.REFRESH ( LIST => 'mv_test', METHOD => 'c', atomic_refresh => FALSE );
3 END;
4 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> SELECT last_refresh_type
2 FROM user_mviews
3 WHERE mview_name = 'MV_TEST';
LAST_REF
--------
COMPLETE
nope, keep trying - please run the example yourself and cut and paste the results.
Here is the complete spool
Pratap, January 10, 2008 - 3:15 pm UTC
SQL> drop table test_base;
Table dropped.
SQL> drop materialized view mv_test;
Materialized view dropped.
SQL> CREATE TABLE test_base
2 (
3 CNTRY_ID VARCHAR2(2 CHAR)
4 )
5 PARTITION BY LIST (CNTRY_ID)
6 (
7 PARTITION RDM_SLSLINE_P_DEU VALUES ('DE')
8 LOGGING
9 NOCOMPRESS,
10 PARTITION RDM_SLSLINE_P_ITA VALUES ('IT')
11 LOGGING
12 NOCOMPRESS,
13 PARTITION RDM_SLSLINE_P_OTHERS VALUES (DEFAULT)
14 LOGGING
15 NOCOMPRESS
16 );
Table created.
SQL> ALTER TABLE test_base ADD PRIMARY KEY (cntry_id);
Table altered.
SQL> INSERT INTO test_base VALUES ( 'IT' );
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE MATERIALIZED VIEW mv_test
2 NOLOGGING COMPRESS BUILD DEFERRED
3 AS
4 SELECT *
5 FROM test_base;
Materialized view created.
SQL> BEGIN
2 dbms_mview.REFRESH('mv_test');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT last_refresh_type, last_refresh_date
2 FROM user_mviews
3 WHERE mview_name = 'MV_TEST';
LAST_REFRESH_TYPE LAST_REFRESH_DA
------------------------ ---------------
COMPLETE 10-JAN-08
SQL> ed
Wrote file afiedt.buf
1 SELECT last_refresh_type, to_char(last_refresh_date, 'hh24:mi:ss')
2 FROM user_mviews
3* WHERE mview_name = 'MV_TEST'
SQL> /
LAST_REFRESH_TYPE TO_CHAR(LAST_REFRESH_DAT
------------------------ ------------------------
COMPLETE 21:12:39
-- First time refresh that is a complete refresh
SQL> BEGIN
2 dbms_mview.REFRESH('mv_test');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT last_refresh_type, to_char(last_refresh_date, 'hh24:mi:ss')
2 FROM user_mviews
3 WHERE mview_name = 'MV_TEST';
LAST_REFRESH_TYPE TO_CHAR(LAST_REFRESH_DAT
------------------------ ------------------------
FAST_PCT 21:13:34
-- Now a fast_pct
SQL> BEGIN
2 dbms_mview.REFRESH ( LIST => 'mv_test', atomic_refresh => FALSE );
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT last_refresh_type, to_char(last_refresh_date, 'hh24:mi:ss')
2 FROM user_mviews
3 WHERE mview_name = 'MV_TEST';
LAST_REFRESH_TYPE TO_CHAR(LAST_REFRESH_DAT
------------------------ ------------------------
FAST_PCT 21:14:24
-- Still a fast_pct with atomic_refresh false
SQL> BEGIN
2 dbms_mview.REFRESH ( LIST => 'mv_test', METHOD => 'c', atomic_refresh => FALSE );
3 END;
4
5 /
PL/SQL procedure successfully completed.
SQL> SELECT last_refresh_type, to_char(last_refresh_date, 'hh24:mi:ss')
2 FROM user_mviews
3 WHERE mview_name = 'MV_TEST';
LAST_REFRESH_TYPE TO_CHAR(LAST_REFRESH_DAT
------------------------ ------------------------
COMPLETE 21:14:51
-- Now it does a complete refresh with direct path load
January 10, 2008 - 5:28 pm UTC
but you have not made any changes.
so - so what? there is no change in the underlying MV, it is still compressed.
ATOMIC_REFRESH in 9i
Megala, January 10, 2008 - 11:37 pm UTC
Tom,
Is it possible to use atomic_refresh => TRUE in 9i in regards to one mview refresh.
In my situation, one materialized view does complete refresh, every 1 hour, during this time, any query accessing this mview should see the old data. So want to use atomic refresh. How to implement this in Oracle 9.2.0.6.
DBMS_MVIEW.REFRESH('CP_SOLUTION', ATOMIC_REFRESH => TRUE) ;
Will it work ?
Thanks for any help!
Thanks!
A reader, January 11, 2008 - 6:41 pm UTC
New in 10g for MV refresh
A reader, January 28, 2008 - 1:00 pm UTC
Just discovered new things about atomic_refresh=false, Oracle makes indexes unsuable and rebuilds them after the refresh. I believe that was not the case before 10g. The only irritant is that it uses parallel indexes rebuilds where I have not specified anywhere to make it use parallel option for rebuild.
Purpose of atomic_refresh
Vijay Bhaskar, September 10, 2008 - 10:50 am UTC
Tom,
With regard to your response on atomic_refresh parameter :-
Oracle document says...
If TRUE, then perform the refresh operations for the specified set of snapshots in a single transaction. This guarantees that either all of the snapshots are successfully refresh or none of the snapshots are refreshed.
And rightly, so as I have even tested this on 9.2.0.6...
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL> create snapshot mv_RTS_SERV_INV_1
2 tablespace RTS_CURRENT_DATA01
3 nologging
4 build deferred
5 refresh complete
6 on demand
7 with rowid
8 as
9 select * from RTS_SERV_INV;
Materialized view created.
And then I complete refreshed the mview...
SQL>exec dbms_mview.refresh('MV_RTS_SERV_INV_1','c');
....
Note that, I haven't specified any value for "atomic_refresh" which means default value would be picked-up & that is TRUE for 9iR2.
And whilst above refresh process is underway, I checked the underlying SQLs being run using v$session & v$sql...
SQL>SELECT SQL_TEXT FROM V$SQL WHERE ADDRESS IN (select SQL_ADDRESS from v$session where username = 'RTS_OWNER' AND STATUS='ACTIVE');
SQL_TEXT
----------------------------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO "RTS_OWNER"."MV_RTS_SERV_INV"(M_ROW$$,"VERSION_NUMBER",....
As you can see, it still picked up the append it...
Can you please throw your views on this...
Best Regards,
Vijay
September 11, 2008 - 11:02 am UTC
a complete refresh is NOT ATOMIC in 9i. (you would need to make a dummy refresh group in order to get an atomic refresh in 9i)
a complete refresh of a single materialized view is
truncate
insert /*+ APPEND */
a complete refresh of a single materialized view in 10g is
delete
insert
in 10g, when atomic refresh is first available, you could say atomic_refresh=>false and 10g will then
truncate
insert /*+ APPEND */
Purpose of atomic_refresh...
Vijay Bhaskar, September 10, 2008 - 10:54 am UTC
Tom,
Apologies for the TYPO error,...
It's infact....
INSERT /*+ APPEND */ INTO "RTS_OWNER"."MV_RTS_SERV_INV_1"(M_ROW$$,"VERSION_NUMBER","SERVICE_IDENTIFIER",.....
Best Regards,
Vijay
appending data with mv and mv logs
Oxnard, February 10, 2010 - 4:57 pm UTC
Looking at the threads I am wondering if this is a bug or at least a gotcha it seems the append hint should be ignored when the table is replicated. I am wondering how to force the transaction to be written to the mv log. I have tried force logging on the tablespace. I cannot ensure if I say do not use the append it, it will be followed:
select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SQL> drop table d1;
Table dropped.
SQL>
SQL> select * from mlog$_d1;
select * from mlog$_d1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table d1 (rn number primary key);
Table created.
SQL>
SQL> create materialized view log on d1;
Materialized view log created.
SQL>
SQL> select * from d1;
no rows selected
SQL>
SQL> select rn,snaptime$$ from mlog$_d1;
no rows selected
SQL>
SQL> insert /*+ append */ into d1 select 1 from dual;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from d1;
RN
----------
1
1 row selected.
SQL>
SQL> select rn,snaptime$$ from mlog$_d1;
no rows selected
SQL>
SQL> insert into d1 select 2 from dual;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from d1;
RN
----------
1
2
2 rows selected.
SQL>
SQL> select rn,snaptime$$ from mlog$_d1;
RN SNAPTIME$$
---------- -----------
2 01-JAN-4000
1 row selected.
SQL>
SQL> drop table d1;
Table dropped.
SQL>
SQL> create table d1 (rn number primary key) logging;
Table created.
SQL>
SQL> create materialized view log on d1 logging;
Materialized view log created.
SQL>
SQL> insert /*+ append */ into d1 select 1 from dual;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from d1;
RN
----------
1
1 row selected.
SQL>
SQL> select rn,snaptime$$ from mlog$_d1;
no rows selected
SQL>
SQL> insert into d1 select 2 from dual;
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from d1;
RN
----------
1
2
2 rows selected.
SQL>
SQL> select rn,snaptime$$ from mlog$_d1;
RN SNAPTIME$$
---------- -----------
2 01-JAN-4000
1 row selected.
February 16, 2010 - 8:10 am UTC
this is not a bug, this is the way it works. When you use /*+ append */, that is a direct path load, it writes ABOVE the high water mark of the table. We therefore know that all rows between two rowids (the old high water mark and the new high water mark) are *new*. Therefore, regardless of the number of rows you insert, we only have to remember two rowids to get them all.
And that is what we do...
ops$tkyte%ORA10GR2> create table t
2 as
3 select * from all_users;
Table created.
ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(user_id);
Table altered.
ops$tkyte%ORA10GR2> create materialized view log on t
2 with sequence, rowid (created)
3 including new values;
Materialized view log created.
ops$tkyte%ORA10GR2> create materialized view mv
2 refresh fast
3 as
4 select created, count(*) from t group by created;
Materialized view created.
ops$tkyte%ORA10GR2> insert into t (username,created,user_id)
2 select /*+ APPEND */ username, created, -user_id
3 from all_users where rownum <= 5;
5 rows created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> select created, m_row$$, snaptime$$,
2 dmltype$$, old_new$$
3 from mlog$_t;
no rows selected
<b>
ops$tkyte%ORA10GR2> select dmloperation, timestamp, lowrowid, highrowid
2 from sys.sumdelta$
3 where tableobj# = (select object_id
4 from dba_objects
5 where object_name = 'T'
6 and object_type='TABLE'
7 and owner = 'OPS$TKYTE')
8 /
D TIMESTAMP LOWROWID HIGHROWID
- -------------------- ------------------ ------------------
I 01-jan-4000 12:00:00 AAA3wgAAEAAALB5AAA AAA3wgAAEAAALB5H//
ops$tkyte%ORA10GR2> select t.*
2 from t, ( select dmloperation, timestamp, lowrowid, highrowid
3 from sys.sumdelta$
4 where tableobj# = (select object_id
5 from dba_objects
6 where object_name = 'T'
7 and object_type = 'TABLE'
8 and owner = 'OPS$TKYTE')
9 ) x
10 where t.rowid between x.lowrowid and x.highrowid
11 /
USERNAME USER_ID CREATED
------------------------------ ---------- --------------------
ASKTOM -558 15-sep-2009 13:29:06
ASKTOM_REDIRECT -557 15-sep-2009 13:18:27
BIG_TABLE -58 14-dec-2005 20:58:32
DIP -19 30-jun-2005 19:14:45
TSMSYS -21 30-jun-2005 19:20:34
ops$tkyte%ORA10GR2> select * from all_sumdelta
2 where tableobj# = (select object_id
3 from dba_objects
4 where object_name = 'T'
5 and object_type = 'TABLE'
6 and owner = 'OPS$TKYTE')
7 /
TABLEOBJ# PARTITIONOBJ# D SCN TIMESTAMP LOWROWID
---------- ------------- - ---------- -------------------- ------------------
HIGHROWID SEQUENCE
------------------ ----------
228384 228384 I 2.8147E+14 01-jan-4000 12:00:00 AAA3wgAAEAAALB5AAA
AAA3wgAAEAAALB5H// 60002
ops$tkyte%ORA10GR2> select staleness from user_mviews;
STALENESS
-------------------
NEEDS_COMPILE
ops$tkyte%ORA10GR2> exec dbms_mview.refresh('MV');
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select staleness from user_mviews;
STALENESS
-------------------
FRESH
ops$tkyte%ORA10GR2> select dmloperation, timestamp, lowrowid, highrowid
2 from sys.sumdelta$
3 where tableobj# = (select object_id
4 from dba_objects
5 where object_name = 'T'
6 and object_type='TABLE'
7 and owner = 'OPS$TKYTE')
8 /
no rows selected
</b>
so, we track them, just not in the mv log - refresh fast works.
A reader, January 26, 2012 - 4:10 pm UTC
That was good one on the MV refresh.
How about this Tom?
When automic_refresh=FALSE, the indexes on the materialized goes to unusable and it is waiting with idle event "pipe get". How do we solve this?
exec dbms_mview.refresh(LIST=>'RECOMMENDED_ORDER_MV',method=>'C',atomic_refresh=>FALSE);
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Thanx
Mani
January 31, 2012 - 3:14 pm UTC
atomic refresh does a
a) truncate (data disappears right away, poof)
b) insert /*+ APPEND */ - direct path load, which maintains indexes
c) commits (data reappears)
this is what it does - the indexes won't/don't go unusable, but the materialized view "disappears" for the duration of the refresh.
Mani, January 31, 2012 - 4:36 pm UTC
Here is the trace, used the same emp example in this thread... The indexes goes to UNUSABLE state.
-- New indexes on EMP table
create index idx_emp_mv_sal on emp_mv(sal);
create bitmap index idx_emp_mv_deptno on emp_mv(deptno);
-- Session trace without Atomic
exec dbms_monitor.session_trace_enable;
exec dbms_mview.refresh( 'EMP_MV' );
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
host tkprof c:\ora11g\136555\diag\rdbms\orc\orc\trace/orc_ora_9380.trc tk.prof sys=no aggregate=yes
Output:
Plan Hash: 175076415
delete from "SCOTT"."EMP_MV"
Plan Hash: 3956160932
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."EMP_MV"("EMPNO","ENAME",
"JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") SELECT "EMP"."EMPNO",
"EMP"."ENAME","EMP"."JOB","EMP"."MGR","EMP"."HIREDATE","EMP"."SAL",
"EMP"."COMM","EMP"."DEPTNO" FROM "EMP" "EMP"
-- Session trace with Atomic
exec dbms_monitor.session_trace_enable;
exec dbms_mview.refresh( 'EMP_MV' , atomic_refresh => FALSE );
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
host tkprof c:\ora11g\136555\diag\rdbms\orc\orc\trace/orc_ora_9380.trc tk.prof sys=no aggregate=yes
Output:
Plan Hash: 2967684236
truncate table "SCOTT"."EMP_MV" purge snapshot log
Plan Hash: 0
ALTER INDEX "SCOTT"."IDX_EMP_MV_DEPTNO" UNUSABLE
Plan Hash: 0
ALTER INDEX "SCOTT"."IDX_EMP_MV_SAL" UNUSABLE
Plan Hash: 0
ALTER INDEX "SCOTT"."PK_EMP1" UNUSABLE
SQL ID: 4xy1z3r121s8x
Plan Hash: 2748781111
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO
"SCOTT"."EMP_MV"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM",
"DEPTNO") SELECT "EMP"."EMPNO","EMP"."ENAME","EMP"."JOB","EMP"."MGR",
"EMP"."HIREDATE","EMP"."SAL","EMP"."COMM","EMP"."DEPTNO" FROM "EMP" "EMP"
BEGIN sys.dbms_index_utl.multi_level_build(index_list=>'"SCOTT"."PK_EMP1"', just_unusable=>TRUE, cont_after_err=>TRUE, concurrent=>TRUE); END;
ALTER INDEX "SCOTT"."PK_EMP1" REBUILD NOPARALLEL
Thanx
Mani
February 01, 2012 - 7:16 am UTC
right, but at the end of the refresh - they are NOT UNUSABLE. They are put back.
As I've said (more than once) with atomic refresh => false, the MV *disappears* during the refresh and comes back after the refresh completes. That is the way it works. If you do not want that, you have to use true - which will use a DELETE and normal INSERT.
Mani, February 02, 2012 - 2:02 pm UTC
Tom - I agree, it puts back the INDEX. The problem is while bringing back the index it is
taking more time and waiting with "pipe get" wait event. We started working on workaround
dropping the index before refresh the MV and create the index manually, that works faster.
The wait event is captured while rebuilding the index.
SQL ID: f29bd06g86mkv
Plan Hash: 4293116352
ALTER INDEX "SCOTT"."PK_EMP1" REBUILD NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 3 1 0
Execute 1 0.00 0.01 2 6 47 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.04 2 9 48 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD UNIQUE PK_EMP1 (cr=19 pr=2 pw=0 time=0 us)(object id 0)
14 SORT CREATE INDEX (cr=4 pr=1 pw=0 time=78 us)
14 MAT_VIEW ACCESS FULL EMP_MV (cr=4 pr=1 pw=0 time=26 us cost=2 size=1066 card=82)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.01 0.01
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.17 0.17 0 0 0 0
Execute 6 0.18 0.80 10 159 23 4
Fetch 4 0.01 0.01 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.37 0.99 10 159 23 6
Misses in library cache during parse: 5
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 7 48.56 63.38
db file sequential read 9 0.01 0.08
asynch descriptor resize 2 0.00 0.00
pipe get 2 0.28 0.29
row cache lock 2 0.16 0.19
February 02, 2012 - 2:50 pm UTC
The problem is while bringing back the
index it is
taking more time and waiting with "pipe get" wait event.
if you want the materialized view to be available all of the time, then you use atomic_refresh=>true.
if you can live with the outage that will take this much time:
a) time to truncate
b) time to insert /*+ append */ into the table
c) time to rebuild indexes
then you use atomic_refresh=>false
We started working on
workaround
dropping the index before refresh the MV and create the index manually, that
works faster.
that does not sound right - since setting unusable takes as long as dropping and rebuilding takes as long as creating.
Numbers - or it didn't happen :)
are you really concerned about 0.29 seconds of wait time?
this thing is teeny tiny - what is the issue?
Atomic Paramter
Yassin Khan, May 08, 2013 - 11:19 am UTC
i am doing group refresh using dbms_refresh.refresh with NOLOGGING and compress I see only insert /*+ bypass_recursive_check */ and there is no APPEND hint.
Now how can i set te ATOMIC_REFRESH parameter to "FALSE" for group refresh ?
Or Any other option available other then ATOMIC_REFRESH parameter ?
Please Advice.
Thanks in advance
Yassin Khan
May 08, 2013 - 1:24 pm UTC
you cannot do a group refresh in that manner - you need atomic to be true for a group refresh by definition, we cannot use truncate+insert append. the truncates would commit (making it not a group anymore). and if you used delete+insert append - well then we would never be able to reuse ANY existing space, each and every refresh would make the materialized views grow and grow and grow non-stop.
Oracle documentation on atomic_refresh
John Keymer, October 16, 2013 - 9:39 am UTC
I hit this page whilst looking for help on the atomic_refresh parameter. I'd originally found the Oracle documentation here (
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_mview.htm#i997194 ) and whilst I agree that what the documentation says is true:
atomic_refresh
If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated.
If this parameter is set to false, then each of the materialized views is refreshed in a separate transaction.I can't help but feel it is quite a poor explanation of the functionality...
November 01, 2013 - 7:03 pm UTC
it has been updated to reference the truncate operation that takes place with atomic_refresh=>false in current documentation
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_mview.htm#ARPLS67204 is that to what you are referring?
also, you can (and should) leave a comment on that page - the doc writer read those constantly and respond to them.
Hint In oracle 11g
Karthik Raj, May 23, 2014 - 5:00 am UTC
HI Tom,
what is the use /* + bypass_recursive_check */ hint in oracle 11g with adv and disadv ? please explain...