ORACLE的Copy命令和create table,insert into的比較

在資料表間複製資料是Oracle DBA經常面對的任務之一，Oracle為這一任務提供了多種解決方案，SQL*Plus Copy 命令便是其中之一。SQL*Plus Copy 命令通過SQL*Net在不同的表(同一伺服器或是不同伺服器)之間複製資料或移動資料。
　在實際執行環境中若能恰當地選擇使用SQL*Plus Copy 命令可以有效地提高資料複製的效能。
　下面將簡要介紹SQL*Plus Copy 命令使用，並在效能方面與其他兩種方案進行對比，力求能提供一個使用Copy 命令的方案參考。
1.語法及使用說明
1.1 語法
下面我們來看一下SQL*Copy 命令的語法及使用說明。
在解釋SQL*Plus Copy 命令的語法之前，我們必須要明確SQL*Plus Copy 命令不是一個方法或是函式，也不是一個SQL語句，它是一個命令(command)，當然這個命令必須在SQL*Plus裡執行。

中國網管聯盟bitsCN.com

 

SQL*Plus Copy 命令的語法：
網管聯盟bitsCN_com

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

我們分部分來解釋一下：

COPY – 這個不太需要解釋，主命令，宣告要執行COPY操作

From Database – 源資料庫
To Database – 目標資料庫
　此處注意花括號中有三種可選的寫法(以”|”隔開)，如果源資料表和目標資料表在同一個Schema中，則可以只寫From Database，也可以只寫To Database，當然還可以是第三種寫法，把From Database和To Database寫全。但如果源資料表和目標資料表不在同一個Schema中，則必須用第三種寫法，即把From Database和To Database都寫全
　From Database和To Database的格式是一樣的：USERID/PASSWORD@SID，這個大家都應該很熟悉了。 網管網www_bitscn_com

{APPEND|CREATE|INSERT|REPLACE} – 宣告運算元據的方式，下面分別解釋一下：
Append – 向已有的目標表中追加記錄，如果目標表不存在，自動建立，這種情況下和Create等效。
Create – 建立目標表並且向其中追加記錄，如果目標表已經存在，則會返回錯誤。
Insert – 向已有的目標表中插入記錄，與Append不同的是，如果目標表不存在，不自動建立而是返回錯誤。
Replace – 用查詢出來的資料覆蓋已有的目標表中的資料，如果目標表不存在，自動建立。

destination_table – 目標表的名字
[(column, column, column, ...)] – 可以指定目標表中列的名字，如果不指定，則自動使用Query中的列名。
USING query – 查詢語句，交流的資料來自這兒。

1.2 使用範例
下面我們通過幾個例子來看一下SQL*Plus Copy 命令的使用：

1.2.1 在同一個伺服器的同一個Schema中複製資料：
同時指定From database和To database
SQL> copy from scott/tiger@lsj to scott/tiger@lsj create dept1 using select * from dept;
只指定From Database
SQL> copy from scott/tiger@lsj create dept2 using select * from dept; 中國網管聯盟bitsCN.com
只指定To Database
SQL> copy to scott/tiger@lsj create dept3 using select * from dept;

1.2.2 在同一個伺服器的不同Schema中複製資料：
這種情況下必須同時指定From Database和To Database
SQL> copy from scott/tiger@lsj to lsjdemo/lsjdemo@lsj create dept using select * from dept;
注意這種情況下，using select * from dept 中並不需要使用scott.demp的形式。

1.2.3 在不同的伺服器間複製資料：
SQL> conn lsj/lsj@sunserve
已連線。
SQL> copy from scott/tiger@lsj to lsj/lsj@sunserve create dept using select * from dept;
2.4 效能實驗結果
實驗資料：
記錄數：5,082,500
資料量：504M
實驗結果

方案------------------------執行時間(秒) ---------Undo(M) ------Redo(M)
Copy command -------------520.51----------------------0 ---------------- 592
Insert into…select …---- 631.64 ------------------345 -------------1720

網管論壇bbs_bitsCN_com

Create Table…------------- 244.79 --------------------0 ----------------515

2.總結

　Create Table…as select…是最快的，而且生成的Undo和Redo資訊最少，所以只要可能，請儘量使用這種方案。但這種方案有一定的限制，即目標表必須是不存在的，不能用它向已有的目標表中追加記錄。
　Insert into … select … 是最慢的，而且生成最多的Undo和Redo資訊，對I/O的壓力最大，優勢在於大家對它比較熟悉，使用起來比較簡單，適合於處理少量的資料，若要處理大量的資料，不推薦使用這種方案。
　Copy Command可以處理Create Table不能處理的情況，即向已有的資料表中追加記錄，相對於insert來說，效率更高一些，生成更少的Redo資訊，不生成Undo資訊，所以在執行大量的資料追加時，推薦使用Copy Command命令。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23577591/viewspace-693916/，如需轉載，請註明出處，否則將追究法律責任。