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