MOVE和CTAS比较(续)

发表于  2012 年 01 月 10 日  由  惜分飞

联系：手机(13429648788) QQ(107644445)

标题：MOVE和CAST比较(续)

作者：惜分飞©版权所有[未经本人同意,请不得以任何形式转载,否则有进一步追究法律责任的权利.]

本篇文章是MOVE和CAST比较的续篇，主要是对于第一篇中没有涉及到的redo和undo的情况加以叙述
1.查询move产生redo和undo量

SQL> alter system flush buffer_cache;   System altered.   SQL> alter system flush shared_pool;   System altered.   SQL> select a. name , b.value    2  from v$statname a, v$mystat b     3  where a.statistic# = b.statistic#    4  and lower (a. name ) in    5  ( 'redo size' , 'undo change vector size' );    NAME                                          VALUE ---------------------------------------- ---------- redo size                                       844 undo change vector size                         136   SQL> ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE TEST_OCP;      Table altered.   SQL> select a. name , b.value    2  from v$statname a, v$mystat b     3  where a.statistic# = b.statistic#    4  and lower (a. name ) in    5  ( 'redo size' , 'undo change vector size' );      NAME                                          VALUE ---------------------------------------- ---------- redo size                                 873074928 undo change vector size                      110748   --产生redo SQL> select 873074928-844 "redo size" from dual;     redo size ----------   873074084   --产生undo SQL> select 110748-136 "undo size" from dual;     undo size ----------      110612

2.查询cast产生redo和undo 大小

SQL> alter system flush buffer_cache;   System altered.   SQL> alter system flush shared_pool;   System altered.   SQL> select a. name , b.value    2  from v$statname a, v$mystat b     3  where a.statistic# = b.statistic# and lower (a. name ) in    4    5  ( 'redo size' , 'undo change vector size' );   NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                               776 undo change vector size                                                 136   SQL> create table chf.t_xifenfei_move_new tablespace users    2  as    3  select * from chf.t_xifenfei_move;    Table created.   SQL> select a. name , b.value    2  from v$statname a, v$mystat b     3  where a.statistic# = b.statistic#    4  and lower (a. name ) in    5  ( 'redo size' , 'undo change vector size' );   NAME                                                                  VALUE ---------------------------------------------------------------- ---------- redo size                                                         873017580 undo change vector size                                              115340   --产生redo SQL> select 873017580-776 "redo size" from dual;     redo size ----------   873016804   --产生undo SQL> select 115340-136 "undo size" from dual;     undo size ----------      115204

3.两次实验比较

--redo(分母使用cast操作产生redo) SQL> select 873074084-873016804 "redo" from dual;          redo ----------       57280   SQL> select 57280/873074084 from dual;   57280/873074084 ---------------       .000065607   --undo(分母使用cast操作产生undo) SQL> select 110612-115204 undo from dual;             undo -------------          -4592   SQL> select 4592/115204 from dual;   4592/115204 -----------   .039859727

通过这两个的比较可以知道move操作产生的redo多了万分之七不到，undo少了百分之四，这些也是在实验允许的误差范围内，再说move操作还包括了cast的一些后续步骤在其中，所以通过这个验证和上一篇试验(MOVE和CAST比较)，基本上可以大胆操作move操作的本质就是全表扫描+append插入数据，操作过程中产生的redo大小几乎和表本身大小(872415232)相等,这个证明,move和cast都是以logging模式运行(数据库本身是非force logging模式)

原文地址：http://www.xifenfei.com/2012/01/move%E5%92%8Ccast%E6%AF%94%E8%BE%83%E7%BB%AD.html