Oracle巧用CTAS快速建立表格

　　CTAS是通過查詢，然后根據查詢的結果來建立表格的一種方式。顯然通過這種方式可以省去定義表結構的方法，而且在創建表的同時導入數據。不過在使用這個方法的時候，仍然有些內容需要引起數據庫管理員的注意。(SQL Server數據庫中成批導入數據的幾個常用方法)

　　如上圖，就是筆者利用CTAS建立表格的過程。其中AD_USER是原表，而AD_USER_CTAS是利用CTAS方法建立的表。利用DESC查看兩個表的結構，大家可以發現起不是簡單的表復制而已。

　　一、可以修改列名和字段的數據類型。

　　大部分時候，在創建表(SQL 2008中控制要求重新創建表的更改)時數據庫管理員可能不希望照搬照抄原由的表格，而總希望能夠更改某些內容。如列的名字或者數據類型等等。在使用CTAS方法來創建表的時候，是可以做到在一定范圍之內對原有的內容進行更改。不過需要注意的是，筆者這里說的是在一定范圍之內可以對原有的內容進行更改，而不是全部內容。即其可以更改的內容是有限的。

　　一般來說，在利用CTAS方法創建表格時，可以更改目標列的列名。如上圖所示，可以直接在Select查詢語句中使用別名，來重定義目標列的名字。然后再利用CTAS來創建表格的時候，就會以目標列的別名來命名新的字段名稱。另外需要注意的是，雖然可以修改數據類型，但是其修改需要受到嚴格的限制。如上圖所示，在SELECT查詢語句中，可以利用數據類型轉換函數對目標數據類型進行轉換，然后在新建立的表格中，就會以目標列的數據類型來定義新列的數據類型。也就是說，如果需要更改某個列的數據類型的話，則必須在SELECT查詢語句中通過數據類型的轉換函數來實現。而不能夠通過其他方法來調整數據類型。除非是表格建立好之后，再在建立好的表格中進行更改。

　　二、約束條件不一定會繼承。

　　利用CTAS建立表格時，原有的約束條件是否會被繼承下來呢?再有些參考書上說，約束條件是不會被保存的。不過根據筆者對ORACLE數據庫的了解，覺得這個說法太過于絕對。其實并不是所有的約束條件都不會被繼承。恰恰相反，一般情況下都會被新表所繼承，而只有在一些特殊的情況下才不會被保留。

　　如上圖所示，在原表中有一個字段叫做VALUE。在創建這個表的時候筆者給其加了一個“非空”的約束條件。利用CTAS創建新表之后，大家可以看到新表中也有一個字段VALUE，而且這個字段的約束條件也是非空的?？梢?，利用CTAS創建新表的時候，原表中的非空約束還是保存下來了。不過這個繼承是有條件的，只有在不對原表中的數據類型進行更改的情況下，才能夠保留這個約束條件。再如上圖所示，筆者在SELECT語句中對NUM數據類型進行了轉換，從數值型數據轉換為字符型數據。在這個過程中，新表的數據類型確實轉換過來了，但是從結果中可以看到，這個字段的約束條件沒有繼承過來。原表中這個字段有一個非空的約束條件，而在目的表中則沒有。

　　根據筆者的了解，只有在更改數據類型的情況下，這個非空等約束條件才不會被保留下來。如果只是更改了目標列的名字(通過查詢列的別名來實現)，如上圖所示的name列，這個約束條件仍然會保留下來。在某些參考書上，對這個約束條件的描述有錯誤。筆者以前在學習的時候，也受其誤導過。各位數據庫管理員在這方面需要引起重視。為了加深理解，各位數據庫管理員可以回去進行測試，以判斷筆者所說的是否準確。

　　三、索引與默認值不一定會被繼承。

　　雖然是否為空等約束條件可以保留下來，但是并不是說目標列中的所有內容都可以被繼承下來。在定義表的時候，除了指定某個列是否為非空之外，還可以設置其他的一些內容。如可以設置某個列的默認值，或者對某個列設置唯一索引等等。那么這些字段的定義在使用CTAS來創建表格的時候是否會被保存下來呢?

　　如上圖所示，在原表中NUM字段設置了一個默認值為0。然后利用CTAS創建表之后，好象這個默認值不存在了。這是否說明這個默認值不會被繼承了呢?其實，上面這個例子中之所以這個默認值沒有在新表中的字段中被保留下來，主要是因為其更改了數據類型，將數值型改為了字符型的數據類型。如果不更改數據類型的話，這個默認值的設置仍然會像非空條件設置那樣被保留下來。即使列名更改了，默認值仍然會被繼承。結合上面的非空條件設置，我們可以得出一個結論，當數據類型被更改了，則針對列的非空條件與默認值的設置，都不會被保留下來。但是如果只是目標列的名字該了，則非空條件與默認值的相關設置會被保留。這一點大家在執行這個操作的時候，需要特別的留意，否則的話就可能會出現問題。

　　雖然默認值在不更改數據類型的情況下，會被保存下來。但是需要注意的是，索引無論在什么情況下都不會被保留(根據筆者的測試)。如在上例中，筆者在原表的VALUE字段中設置了一個唯一索引。此時即使不更改字段VALUE字段的數據類型或者目標列的名字，其在新表中也不會保留下來。所以，索引跟默認值、非空條件是不一樣的。至少在CTAS方案中他們體現出了不一樣的特性。

　　四、可以只復制表結構，而不插入記錄。

　　在利用CTAS來創建新表的時候，出了可以創建表結構之外，還可以將相關的記錄也插入到目的表中。但是有時候數據庫管理員可能不需要插入記錄，而只需要一個表結構即可。其實要實現這個需求也是很簡單的。如上例中，即使原表中沒有任何記錄，只要這些字段存在，其仍然可以創建新表。這可以給我們什么啟示呢?其實如果SELECT語句沒有返回任何記錄的話，那么CTAS語句就不會往新表中插入記錄。在執行CTAS語句時可以利用WHERE條件語句來過濾記錄。如只插入部分數據等等。在設置這個WHERE條件語句時，如果沒有一條記錄符合這個條件，那么系統就只會返回表的框架結構。此時就可以復制表的結構，而不插入任何記錄了。

　　五、提高執行效率，最好不做日志記錄。

　　當新表中的記錄比較多而沒有采用WHERE條件語句進行過濾(或者過濾后仍然有很多的記錄)，則在執行這個語句時可能會花費比較長的時間。因為在默認情況下，往數據庫表中插入數據的時候，不僅需要更新記錄(數據高速緩存或者數據文件)，而且還需要更新相關的日志(重做日志高速緩存或者重做日志文件)。為此當需要插入大量記錄的時候，其速度就會比較慢，如會頻繁的觸發日志切換事件或者會觸發DBWR進程等等。為此在往數據庫中插入大量數據的時候，最好在語句中使用NOLOGGING選項，讓數據庫不記錄相關的重做日志信息，以提高插入的速度。另外為了保障數據的安全，最好在插入完成之后，馬上對數據庫或者相關的表空間進行備份。以為此時由于相關更新沒有記錄到重做日志中，為此當出現故障時將很難恢復。

原文轉自：http://www.anti-gravitydesign.com