Skip to Main Content
  • Questions
  • Materialized view refresh - Data compression

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Pratap.

Asked: January 09, 2008 - 9:40 am UTC

Last updated: November 01, 2013 - 7:03 pm UTC

Version: 10.2.0.3.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

We have an MV that is built with NOLOGGING and compress. But after refresh, there is no reduction in the total block numbers as compared to the table. When I see the recursive SQL, I see only insert /*+ bypass_recursive_check */ and there is no APPEND hint there. So I believe there is no direct path load happening. Is that correct? In such a case how can I achieve a direct path load and compression in an MV refresh.

Note that we have compared the size of MV and loaded the same data in a table. The size of the table is half that of MV due to compression.

Regards,
Pratap

and Tom said...

ATOMIC_REFRESH

if you set that to FALSE, it'll do a truncate + insert /*+ append */ on a FULL refresh.

Beware that of course the data will disappear during the refresh - as long as you are OK with that, it'll direct path.

eg, if you run something like:

connect /

drop table emp;
drop materialized view emp_mv;

create table emp as select * from scott.emp;
alter table emp add constraint emp_pk primary key(empno);

create materialized view emp_mv
refresh complete
as
select * from emp;

exec dbms_monitor.session_trace_enable
exec dbms_mview.refresh( 'EMP_MV' );

column trace new_val TRACE

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'
/

disconnect
!tkprof &TRACE ./tk.prf sys=no aggregate=yes
connect /
edit tk.prf

exec dbms_monitor.session_trace_enable
exec dbms_mview.refresh( 'EMP_MV' , atomic_refresh => FALSE );
column trace new_val TRACE

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'
/

disconnect
!tkprof &TRACE ./tk.prf sys=no aggregate=yes
connect /
edit tk.prf


tkprof will show the first time that you:

delete from "OPS$TKYTE"."EMP_MV"

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "OPS$TKYTE"."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"


and the second time you did:

truncate table "OPS$TKYTE"."EMP_MV" purge snapshot log

INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO
  "OPS$TKYTE"."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"


Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Tom Kyte
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;
Tom Kyte
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


Tom Kyte
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!


Tom Kyte
January 11, 2008 - 7:04 am UTC

In 9i and before, you can use a small snapshot group to have atomic_refresh do that

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1389964969205

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


Tom Kyte
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.



Tom Kyte
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
Tom Kyte
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
Tom Kyte
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
Tom Kyte
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


Tom Kyte
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...
Tom Kyte
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...

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions