MOVE和CTAS比較(續)

發表于  2012 年 01 月 10 日  由  惜分飛

聯系：手機[phone]) 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