ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何减少刷新物化视图时redo的大小-转贴?

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

原创 Linux操作系统 作者:myfriend2010 时间:2019-06-18 07:03:05 0 删除 编辑

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的产生,因此速度上也会快很多:

a) disable indexes
b) call refresh
c) rebuild indexes with no logging

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

请登录后发表评论 登录
全部评论

注册时间:2018-09-01

  • 博文量
    187
  • 访问量
    126695