本篇文章是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模式)
