CTAS VS create table and then insert

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

<a href="http://blog.51cto.com/maclean/1277556#">?</a>

<code>SQL&gt; </code><code>select</code> <code>* </code><code>from</code> <code>v$version;</code>

<code>BANNER</code>

<code>----------------------------------------------------------------</code>

<code>Oracle </code><code>Database</code> <code>10g Enterprise Edition Release 10.2.0.4.0 - 64bi</code>

<code>PL/SQL Release 10.2.0.4.0 - Production</code>

<code>CORE    10.2.0.4.0      Production</code>

<code>TNS </code><code>for</code> <code>Linux: Version 10.2.0.4.0 - Production</code>

<code>NLSRTL Version 10.2.0.4.0 - Production</code>

<code>SQL&gt; archive log list;</code>

<code>Database</code> <code>log mode              Archive Mode</code>

<code>Automatic archival             Enabled</code>

<code>Archive destination            USE_DB_RECOVERY_FILE_DEST</code>

<code>Oldest online log </code><code>sequence</code>     <code>1</code>

<code>Next</code> <code>log </code><code>sequence</code> <code>to</code> <code>archive   2</code>

<code>Current</code> <code>log </code><code>sequence</code>           <code>2</code>

<code>/* 数据库处于归档模式下 */</code>

<code>SQL&gt; </code><code>select</code> <code>ss.</code><code>name</code><code>,ms.value  </code><code>from</code> <code>v$mystat ms,v$sysstat ss</code>

<code>  </code><code>2  </code><code>where</code>

<code>  </code><code>3  ms.statistic#=ss.statistic#</code>

<code>  </code><code>4  </code><code>and</code> <code>ss.</code><code>name</code> <code>in</code> <code>(</code><code>'undo change vector size'</code><code>,</code><code>'redo size'</code><code>);</code>

<code>NAME</code>                                                                  <code>VALUE</code>

<code>---------------------------------------------------------------- ----------</code>

<code>redo </code><code>size</code>                                                                 <code>0</code>

<code>undo change vector </code><code>size</code>                                                   <code>0</code>

<code>SQL&gt; </code><code>create</code> <code>table</code> <code>YOUYUS </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>dba_objects;</code>

<code>Table</code> <code>created.</code>

<code>redo </code><code>size</code>                                                           <code>5783384</code>

<code>undo change vector </code><code>size</code>                                               <code>15408</code>

<code>/* CTAS方式产生了少量的undo，可以猜想其使用直接路径方式插入，Oracle仅产生维护数据字典的undo */</code>

<code>SQL&gt; </code><code>drop</code> <code>table</code> <code>YOUYUS;</code>

<code>Table</code> <code>dropped.</code>

<code>SQL&gt; conn / </code><code>as</code> <code>sysdba</code>

<code>Connected.</code>

<code>/* 清理现场 */</code>

<code>SQL&gt; </code><code>create</code> <code>table</code> <code>YOUYUS </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>dba_objects </code><code>where</code> <code>0=1;</code>

<code>redo </code><code>size</code>                                                             <code>19492</code>

<code>undo change vector </code><code>size</code>                                                <code>5680</code>

<code>/* 建立空表YOUYUS，同样需要维护数据字典  */</code>

<code>SQL&gt; </code><code>insert</code> <code>into</code> <code>YOUYUS </code><code>select</code> <code>* </code><code>from</code> <code>dba_objects;</code>

<code>50729 </code><code>rows</code> <code>created.</code>

<code>SQL&gt; </code><code>commit</code><code>;</code>

<code>Commit</code> <code>complete.</code>

<code>redo </code><code>size</code>                                                           <code>5743540</code>

<code>undo change vector </code><code>size</code>                                              <code>203904</code>

<code>/* 普通</code><code>insert</code><code>操作产生了远大于CTAS的undo */</code>

<code>SQL&gt; </code><code>insert</code> <code>/*+ append */ </code><code>into</code> <code>YOUYUS </code><code>select</code> <code>* </code><code>from</code> <code>dba_objects;</code>

<code>redo </code><code>size</code>                                                           <code>5781712</code>

<code>undo change vector </code><code>size</code>                                               <code>14808</code>

<code>/* 建表后直接路径插入方式与CTAS产生的redo和undo数量大致相仿 */</code>

从资源消耗的角度讲CTAS或直接路径插入方式有一定优势，如果启用nologging选项的话这种优势会更加显著。

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