CTAS和insert append的一個測試

該文章寫於2004年，最近剛從以前的blog上遷移出來。

[@more@]

8174上的一個測試，非歸檔模式：

程式碼:
 
SQL> select * from v$version; 
 BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production 
PL/SQL Release 8.1.7.4.0 - Production 
CORE    8.1.7.2.1       Production 
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production 
NLSRTL Version 3.4.1.0.0 - Production 
 
SQL> archive log list 資料庫日誌模式             非存檔模式 
自動存檔             啟用 
存檔終點            D:databaseoracleora817RDBMS 
最早的概要資訊日誌序列     1488 
當前日誌序列           1491   
 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
     91848 
 
SQL> create table test nologging as select * from all_objects; 
 表已建立。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
    147148 
 
SQL> drop table test; 
 表已丟棄。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
    177584 
 
SQL> create table test as select * from all_objects; 
 表已建立。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
    232892        
 
SQL> select (232892 - 177584 ) redo,(147148-91848) redo_nolog from dual; 
 
      REDO REDO_NOLOG ---------- ---------- 
     55308      55300 '


在歸檔模式下的情況：
程式碼:
 
SQL> shutdown immediate; 資料庫已經關閉。 
已經解除安裝資料庫。 
ORACLE 例程已經關閉。 
SQL> startup mount 
ORACLE 例程已經啟動。 
 
Total System Global Area   65648668 bytes 
Fixed Size                    75804 bytes 
Variable Size              44523520 bytes 
Database Buffers           20971520 bytes 
Redo Buffers                  77824 bytes 
資料庫裝載完畢。 
SQL> alter database archivelog 
  2  / 
 資料庫已更改。 
 
SQL> alter database open; 
 資料庫已更改。     
 
SQL> drop table test; 
 表已丟棄。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
     30520 
      
SQL> create table test as select * from all_objects; 
 表已建立。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
   2953668 
 
SQL> drop table test; 
 表已丟棄。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
   3070020 
 
SQL> create table test nologging as select * from all_objects; 
 表已建立。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
   3125328 
 
SQL> select (2953668-30520) redo,(3125328-3070020) redo_nolog from dual; 
 
      REDO REDO_NOLOG ---------- ---------- 
   2923148      55308 '


在歸檔模式下的近一步測試，比較ctas和ctas無資料+ insert append 的redo size：
程式碼:
 
SQL> drop table test; 
 表已丟棄。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
   3155764 
 
SQL> create table test as select * from all_objects where 1=0; 
 表已建立。 
 
SQL> insert /*+append*/into test select * from all_objects; 
 已建立25474行。 
 
SQL> commit; 
 提交完成。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
   6079860 
    
SQL> select  (6079860-3155764) logging from dual; 
 
(6079860-3155764) 
----------------- 
          2924096    
 
    
SQL> drop table test; 
 表已丟棄。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
   6110356 
 
SQL> create table test nologging as select * from all_objects where 1=0; 
 表已建立。 
 
SQL> insert /*+append*/ into test select * from all_objects; 
 已建立25474行。 
 
SQL> commit; 
 提交完成。 
 
SQL> select * from redo_size; 
 
     VALUE ---------- 
   6167588 
    
SQL> select (6079860-3155764) logging ,(6167588-6110356)nologging from dual; 
 
   LOGGING  NOLOGGING ---------- ---------- 
   2924096      57232 '


根據這個結果，我們看到：
noarchivelog下的CTAS 的redo＝noarchivelog下的CTAS nologging的redo ＝archivelog下的CTAS nologging的redo
這三種情況下，都對系統產生了較少的redo size
只有在archivelog 下CTAS，才產生了較多的redo size

比較了CTAS和CTAS＋insert append後，實際上（archivelog mode）：
CTAS nologging redo ＝CTAS(no data) nologing +insert append redo size; 

CTAS redo ＝CTAS(no data) +insert append redo size;