CTAS VS create table and then insert

很多情况下我们都会需要复制源表数据以达到冗余数据的目的，那么到底是使用CREATE TABLE AS SELECT的CTAS方式，还是先建好表的结构然后再插入数据好呢？ 我们来看看这2种方式的不同表现：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83

/* 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选项的话这种优势会更加显著。

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277556