create table as select 與create table後insert方式生成的undo與redo對比

 測試環境：OS:RHEL 5.4 X86  DB:10.2.0.4  歸檔模式

      下面是測試結論，此結論隻是在本測試環境有效。

1，ctas與create table後insert語句産生的redo是差不多的。 

2，ctas生成的undo遠遠小于create table and insert方式。 

3，ctas生成的undo與create table後insert /*+ append */差不多。 

4，ctas nologging方法生成的log遠遠小于其它的方式。 

5，append方式并不一定能減少redo的生成，但是肯定能減少undo的生成。 

6，append減少redo，前提是表在nologging方式下面，注意這裡表上面沒有索引，append隻對表有效，對索引無效。

     下面是詳細的測試步驟

SQL> SELECT a.name, b.VALUE 

  2    FROM v$sysstat a, v$mystat b 

  3   WHERE     a.statistic# = b.statistic# 

  4         AND a.name IN ('redo size', 'undo change vector size'); 

NAME                                                                  VALUE 

---------------------------------------------------------------- ---------- 

redo size                                                                 0 

undo change vector size                                                   0 

SQL> create table scott.test_ctas as select * from dba_objects; 

SQL> /* Formatted on 2013/3/8 22:07:44 (QP5 v5.240.12305.39476) */ 

redo size                                                           5794552 

undo change vector size                                               23812 

SQL> create table scott.test_ctas_nologging nologging as select * from dba_objects; 

Table created. 

redo size                                                             88576 

undo change vector size                                               22008 

這種方式生成的UNDO,REDO的大小都是最好的， 

SQL> SELECT a.name, b.VALUE                                         

SQL> create table scott.test_normal as select * from dba_objects where 1=0; 

redo size                                                             19848 

undo change vector size                                                5712 

SQL> insert into scott.test_normal select * from dba_objects; 

50350 rows created. 

redo size                                                           5725444 

undo change vector size                                              208092 

SQL> create table scott.test_normal_append as select * from dba_objects where 1=2; 

redo size                                                             21224 

undo change vector size                                                6072 

SQL> insert /*+ append */ into scott.test_normal_append select * from dba_objects;     

redo size                                                           5771092 

undo change vector size                                               21072 

  4         AND a.name IN ('redo size', 'undo change vector size');  

SQL> create table scott.test_normal_append_nologging nologging as select * from dba_objects where 1=2; 

redo size                                                             37568 

undo change vector size                                               14204 

SQL> insert /*+ append */ into scott.test_normal_append_nologging select * from dba_objects;   

50351 rows created. 

redo size                                                            142944 

undo change vector size                                               46744 

     本文轉自7343696 51CTO部落格，原文連結：http://blog.51cto.com/luoping/1150464，如需轉載請自行聯系原作者