很多情况下我们都会需要复制源表数据以达到冗余数据的目的，那么到底是使用CREATE TABLE AS SELECT的CTAS方式，还是先建好表的结构然后再插入数据好呢？
我们来看看这2种方式的不同表现：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
/* 数据库处于归档模式下 */
SQL> select ss.name,ms.value from v$mystat ms,v$sysstat ss
2 where
3 ms.statistic#=ss.statistic#
4 and ss.name in ('undo change vector size','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 0
undo change vector size 0
SQL> create table YOUYUS as select * from dba_objects;
Table created.
SQL> select ss.name,ms.value from v$mystat ms,v$sysstat ss
2 where
3 ms.statistic#=ss.statistic#
4 and ss.name in ('undo change vector size','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 5783384
undo change vector size 15408
/* CTAS方式产生了少量的undo，可以猜想其使用直接路径方式插入，Oracle仅产生维护数据字典的undo */
SQL> drop table YOUYUS;
Table dropped.
SQL> conn / as sysdba
Connected.
/* 清理现场 */
SQL> create table YOUYUS as select * from dba_objects where 0=1;
Table created.
SQL> select ss.name,ms.value from v$mystat ms,v$sysstat ss
2 where
3 ms.statistic#=ss.statistic#
4 and ss.name in ('undo change vector size','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 19492
undo change vector size 5680
/* 建立空表YOUYUS，同样需要维护数据字典 */
SQL> insert into YOUYUS select * from dba_objects;
50729 rows created.
SQL> commit;
Commit complete.
SQL> select ss.name,ms.value from v$mystat ms,v$sysstat ss
2 where
3 ms.statistic#=ss.statistic#
4 and ss.name in ('undo change vector size','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 5743540
undo change vector size 203904
/* 普通insert操作产生了远大于CTAS的undo */
SQL> drop table YOUYUS;
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> select ss.name,ms.value from v$mystat ms,v$sysstat ss
2 where
3 ms.statistic#=ss.statistic#
4 and ss.name in ('undo change vector size','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 0
undo change vector size 0
SQL> create table YOUYUS as select * from dba_objects where 0=1;
Table created.
SQL> insert /*+ append */ into YOUYUS select * from dba_objects;
50729 rows created.
SQL> commit;
Commit complete.
SQL> select ss.name,ms.value from v$mystat ms,v$sysstat ss
2 where
3 ms.statistic#=ss.statistic#
4 and ss.name in ('undo change vector size','redo size');
NAME VALUE
---------------------------------------------------------------- ----------
redo size 5781712
undo change vector size 14808
/* 建表后直接路径插入方式与CTAS产生的redo和undo数量大致相仿 */
从资源消耗的角度讲CTAS或直接路径插入方式有一定优势，如果启用nologging选项的话这种优势会更加显著。