如何减少刷新物化视图时redo的大小-转贴?

SQL> create table testabc(Id number ,description char(1000));

Table created

SQL> alter table testabc add primary key(id);

Table altered

SQL>for i in 1.. 50001

loop

insert into testabc values(i,to_char(i))

end loop;

PL/SQL procedure successfully completed

SQL> select count(*) from testabc;

COUNT(*)

----------

50001

1. 物化视图上没有索引，且没有设置nologging

SQL> create materialized view testabc_mv as select * from testabc;

Materialized view created

SQL> column value new_val V

SQL> set verify off

SQL>

SQL> select a.name, b.value

2 from v$statname a, v$mystat b

3 where a.statistic# = b.statistic#

4 and a.name = 'redo size'

5 /

NAME VALUE

---------------------------------------------------------------- ----------

redo size 63358940

SQL> exec dbms_mview.refresh( 'testabc_mv', 'C' );

PL/SQL procedure successfully completed

SQL>

SQL> select a.name, b.value, to_char( b.value-&V,

2 '999,999,999,999' ) diff from v$statname a, v$mystat b

3 where a.statistic# = b.statistic#

4 and a.name = 'redo size'

5 ;

NAME VALUE DIFF

---------------------------------------------------------------- ---------- ----------------

redo size 125733284 62,374,344

2.设置物化视图nologging;

SQL> alter table testabc_mv nologging;

Table altered

SQL> exec dbms_mview.refresh( 'testabc_mv', 'C' );

PL/SQL procedure successfully completed

SQL>

SQL> select a.name, b.value, to_char( b.value-&V,

2 '999,999,999,999' ) diff from v$statname a, v$mystat b

3 where a.statistic# = b.statistic#

4 and a.name = 'redo size'

5 ;

NAME VALUE DIFF

---------------------------------------------------------------- ---------- ----------------

redo size 128937592 3,204,308

3.在物化视图上创建索引

SQL> create index ind_testabc_mv on testabc_Mv(description) ;

Index created

SQL>

SQL> select a.name, b.value

2 from v$statname a, v$mystat b

3 where a.statistic# = b.statistic#

4 and a.name = 'redo size'

5 ;

NAME VALUE

---------------------------------------------------------------- ----------

redo size 187908916

SQL> exec dbms_mview.refresh( 'testabc_mv', 'C' );

PL/SQL procedure successfully completed

SQL>

SQL> select a.name, b.value, to_char( b.value-&V,

2 '999,999,999,999' ) diff from v$statname a, v$mystat b

3 where a.statistic# = b.statistic#

4 and a.name = 'redo size'

5 ;

NAME VALUE DIFF

---------------------------------------------------------------- ---------- ----------------

redo size 682009972 494,101,056

由以上可得得出,redo的变化:

方法	Redo变化
正常	62,374,344
加nologing	3,204,308
加索引	494,101,056

当刷新物化视图时，作以下准备大大减少redo的产生，因此速度上也会快很多:

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-51402/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-51402/