普通表与临时表DML操作会产生REDO/UNDO对比与分析(三)
三、创建一个ON COMMIT DELETE ROWS 临时表，并统计建表及插入数据等操作所产生的REDO及UNDO大小
PRESERVE ROWS临时表中的测试和ON COMMIT DELETE ROWS结果类似，不再重复贴了。
在上一步做完后退出SQLPLUS再登陆进行操作。
建表前后的REDO/UNDO大小变化
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 1956
undo change vector size 164
BYS@bys1>create global temporary table temp1 on commit delete rows as select * from test9 where 1=0;
Table created.
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 26404
undo change vector size 6692
插入数据前后的REDO/UNDO大小变化
BYS@bys1>insert into temp1 select * from test9;
6957120 rows created.
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 43254212
undo change vector size 30540820
BYS@bys1>select count(*) from temp1;
COUNT(*)
----------
6957120
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 43254212
undo change vector size 30540820
提交前后的REDO/UNDO大小变化
BYS@bys1>commit;
Commit complete.
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 43254448
undo change vector size 30540820
查询前后的REDO/UNDO大小变化：--无变化
BYS@bys1>select count(*) from temp1;
COUNT(*)
----------
0
BYS@bys1>select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 43254448
undo change vector size 30540820
统计情况如下：
create global temporary table temp1语句： 产生REDO和UNDO分别为： 24448 6528
insert into temp1 select * from dba_objects;语句：产生REDO和UNDO分别为：43227808 3