Oracle20111213--數(shù)據(jù)導入方法比較

1、Oracle 數(shù)據(jù)導入方法比較每個數(shù)據(jù)庫管理員都會面臨數(shù)據(jù)導入的問題，這有可能發(fā)生在數(shù)據(jù)庫的新老移植過程中，或者是在數(shù)據(jù)庫崩潰后的恢復重建過程中，還有可能是在創(chuàng)建測試數(shù)據(jù)庫的模擬環(huán)境過程中，總之作為一名合格的數(shù)據(jù)庫管理員，你應該做好接受各種數(shù)據(jù)導入請求的技術儲備，同時還要盡量滿足人本能的對導入速度的苛求。本文僅針對 Oracle 數(shù)據(jù)庫所提供的加速數(shù)據(jù)導入的各種特性和技術進行探討，其中的一些方法也可以轉(zhuǎn)化應用于其他數(shù)據(jù)庫。以下七種數(shù)據(jù)導入方法哪個最適用需要針對具體情況具體分析，我也附帶列舉了影響導入速度的各種因素供斟酌。為了比較各種數(shù)據(jù)導入方法的效果，我創(chuàng)建了示例表和數(shù)據(jù)集，并用各種方法導入示

2、例數(shù)據(jù)集來計算總體導入時間和導入進程占用 CPU 時間，這里得出的時間僅供參考。需要說明的是，建議你使用 Oracle 9i 企業(yè)版數(shù)據(jù)庫，當然你也可以嘗試使用 Oracle 7.3 以上的標準版數(shù)據(jù)庫。本文使用的機器配置為：CPU Intel P4，內(nèi)存 256M，數(shù)據(jù)庫 Oracle 9i 企業(yè)版 示例表結(jié)構和數(shù)據(jù)集為了演示和比較各種數(shù)據(jù)導入方法，我假定數(shù)據(jù)導入任務是將外部文件數(shù)據(jù)導入到 Oracle 數(shù)據(jù)庫的CALLS表中，外部數(shù)據(jù)文件包含十萬條呼叫中心記錄，將近 6MB 的文件大小，具體的數(shù)據(jù)示例如下:82302284384,2003-04-18:13:18:58,5001,投訴,手機

3、三包維修質(zhì)量82302284385,2003-04-18:13:18:59,3352,咨詢,供水熱線的號碼82302284386,2003-04-18:13:19:01,3142,建議,增設公交線路 接受導入數(shù)據(jù)的表名是 CALLS，表結(jié)構如下： Name Null? Type Comment - - - - CALL_ID NOT NULL NUMBER Primary key CALL_DATE NOT NULL DATE Non-unique index EMP_ID NOT NULL NUMBER CALL_TYPE NOT NULL VARCHAR2(12) DETAILS VARC

4、HAR2(25)逐條數(shù)據(jù)插入INSERT 數(shù)據(jù)導入的最簡單方法就是編寫 INSERT 語句，將數(shù)據(jù)逐條插入數(shù)據(jù)庫。這種方法只適合導入少量數(shù)據(jù)，如 SQL*Plus 腳本創(chuàng)建某個表的種子數(shù)據(jù)。該方法的最大缺點就是導入速度緩慢，占用了大量的 CPU 處理時間，不適合大批量數(shù)據(jù)的導入；而其主要優(yōu)點就是導入構思簡單又有修改完善的彈性，不需要多做其它的準備就可以使用。如果你有很多時間沒法打發(fā)，又想折磨一下數(shù)據(jù)庫和 CPU，那這種方法正適合你。:) 為了與其它方法做比較，現(xiàn)將十萬條記錄通過此方法導入到 CALLS 表中，總共消耗 172 秒，其中導入進程占用 CPU 時間為 52 秒。逐條數(shù)據(jù)插入 INS

5、ERT，表暫無索引 為什么上一種方法占用了較多的 CPU 處理時間，關鍵是 CALLS 表中已創(chuàng)建了索引，當一條數(shù)據(jù)插入到表中時，Oracle 需要判別新數(shù)據(jù)與老數(shù)據(jù)在索引方面是否有沖突，同時要更新表中的所有索引，重復更新索引會消耗一定的時間。因此提高導入速度的好辦法就是在創(chuàng)建表時先不創(chuàng)建索引或者在導入數(shù)據(jù)之前刪除所有索引，在外部文件數(shù)據(jù)逐條插入到表中后再統(tǒng)一創(chuàng)建表的索引。這樣導入速度會提高，同時創(chuàng)建的索引也很緊湊而有效，這一原則同樣適用于位圖索引（Bitmap Index）。對于主要的和唯一的關鍵約束(key constraints)，可以使之先暫時失效(disabling)或者刪除約束來獲

6、得同樣的效果，當然這些做法會對已經(jīng)存在的表的外鍵約束產(chǎn)生相關的影響，在刪除前需要通盤斟酌。 需要說明的是，這種方法在表中已存在很多數(shù)據(jù)的情況下不太合適。例如表中已有九千萬條數(shù)據(jù)，而此時需要追加插入一千萬條數(shù)據(jù)，實際導入數(shù)據(jù)節(jié)省的時間將會被重新創(chuàng)建一億條數(shù)據(jù)的索引所消耗殆盡，這是我們不希望得到的結(jié)果。但是，如果要導入數(shù)據(jù)的表是空的或?qū)氲臄?shù)據(jù)量比已有的數(shù)據(jù)量要大得多，那么導入數(shù)據(jù)節(jié)省的時間將會少量用于重新創(chuàng)建索引，這時該方法才可以考慮使用。 加快索引創(chuàng)建是另一個需要考慮的問題。為了減少索引創(chuàng)建中排序的工作時間，可以在當前會話中增加 SORT_AREA_SIZE 參數(shù)的大小，該參數(shù)允許當前會話在內(nèi)

7、存的索引創(chuàng)建過程中執(zhí)行更多的排序操作。同樣還可以使用 NOLOGGING 關鍵字來減少因創(chuàng)建索引而生成的 REDO 日志量，NOLOGGING 關鍵字會對數(shù)據(jù)庫的恢復和 Standby 備用數(shù)據(jù)庫產(chǎn)生明顯的影響，所以在使用之前要仔細斟酌，到底是速度優(yōu)先還是穩(wěn)定優(yōu)先。運用這種方法，先刪除 CALLS 表的主鍵和不唯一的索引，然后逐條導入數(shù)據(jù)，完成后重新創(chuàng)建索引( 表在導入數(shù)據(jù)前是空的)。該方法總共消耗 130 秒，包括重建索引的時間，其中導入進程占用 CPU 時間為 35秒。 這種方法的優(yōu)點是可以加快導入的速度并使索引更加緊湊有效；缺點是缺乏通用性，當你對表增加新的復雜的模式元素（索引、外鍵等）

8、時你需要添加代碼、修改導入執(zhí)行程序。另外針對 7*24 在線要求的數(shù)據(jù)庫在線導入操作時，刪除表的索引會對在線用戶的查詢有很大的性能影響，同時也要考慮，主要或唯一的關鍵約束條件的刪除或失效可能會影響到引用它們的外鍵的使用。批量插入，表暫無索引 在Oracle V6 中 OCI 編程接口加入了數(shù)組接口特性。數(shù)組操作允許導入程序讀取外部文件數(shù)據(jù)并解析后，向數(shù)據(jù)庫提交SQL語句，批量插入 SQL 語句檢索出的數(shù)據(jù)。Oracle 僅需要執(zhí)行一次 SQL 語句，然后在內(nèi)存中批量解析提供的數(shù)據(jù)。批量導入操作比逐行插入重復操作更有效率，這是因為只需一次解析 SQL 語句，一些數(shù)據(jù)綁訂操作以及程序與數(shù)據(jù)庫之間來

9、回的操作都顯著減少，而且數(shù)據(jù)庫對每一條數(shù)據(jù)的操作都是重復可知的，這給數(shù)據(jù)庫提供了優(yōu)化執(zhí)行的可能。其優(yōu)點是數(shù)據(jù)導入的總體時間明顯減少，特別是進程占用 CPU 的時間。 需要提醒的是，通過 OCI 接口確實可以執(zhí)行數(shù)據(jù)批量導入操作，但是許多工具和腳本語言卻不支持使用此功能。如果要使用該方法，需要研究你所使用的開發(fā)工具是否支持 OCI 批量操作功能。導入程序需要進行復雜的編碼并可能存在錯誤的風險，缺乏一定的彈性。 運用上述方法，程序?qū)⑼獠繑?shù)據(jù)提取到內(nèi)存中的數(shù)組里，并執(zhí)行批量插入操作（100行/次），保留了表的刪除/重建索引操作，總的導入時間下降到 14 秒，而進程占用 CPU 的時間下降到7秒，可見

10、實際導入數(shù)據(jù)所花費的時間顯著下降了 95%。CREATE TABLE AS SELECT，使用Oracle9i的External Table Oracle 9i 的一項新特性就是 External Table，它就象通常的數(shù)據(jù)庫表一樣，擁有字段和數(shù)據(jù)類型約束，并且可以查詢，但是表中的數(shù)據(jù)卻不存儲在數(shù)據(jù)庫中，而是在與數(shù)據(jù)庫相關聯(lián)的普通外部文件里。當你查詢 External Table 時，Oracle 將解析該文件并返回符合條件的數(shù)據(jù)，就象該數(shù)據(jù)存儲在數(shù)據(jù)庫表中一樣。 需要注意的是，你可以在查詢語句中將 External Table 與數(shù)據(jù)庫中其他表進行連接（Join），但是不能給 Extern

11、al Table 加上索引，并且不能插入/更新/刪除數(shù)據(jù)，畢竟它不是真正的數(shù)據(jù)庫表。另外，如果與數(shù)據(jù)庫相關聯(lián)的外部文件被改變或者被刪除，這會影響到 External Table 返回查詢結(jié)果，所以在變動前要先跟數(shù)據(jù)庫打招呼。 這種方法為導入數(shù)據(jù)打開了新的一扇門。你可以很容易的將外部文件與數(shù)據(jù)庫相關聯(lián)，并且在數(shù)據(jù)庫中創(chuàng)建對應的 External Table，然后就可以立即查詢數(shù)據(jù)，就象外部數(shù)據(jù)已經(jīng)導入到數(shù)據(jù)庫表中一樣。唯一的不足需要明確，數(shù)據(jù)并未真正導入到數(shù)據(jù)庫中，當外部文件被刪除或覆蓋時，數(shù)據(jù)庫將不能訪問 External Table 里的數(shù)據(jù)，而且索引沒有被創(chuàng)建，訪問數(shù)據(jù)速度將有所緩慢。創(chuàng)建

12、 CALLS_EXTERNAL（External Table表）如下，使之與外部數(shù)據(jù)文件關聯(lián)： CREATE TABLE calls_external (call_id NUMBER, call_date DATE, emp_id NUMBER, call_type VARCHAR2(12), details VARCHAR2(25) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY extract_files_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELD

13、S TERMINATED BY , MISSING FIELD VALUES ARE NULL (call_id, call_date CHAR DATE_FORMAT DATE MASK yyy-mm-dd:hh24:mi:ss, emp_id, call_type, details ) ) LOCATION (calls.dat) ); 然后將 External Table 與真正被使用的表 CALLS 關聯(lián)同步，刪除 CALLS 表并重建它： CREATE TABLE calls ( call_id NUMBER NOT NULL, call_date DATE NOT NULL, em

14、p_id NUMBER NOT NULL, call_type VARCHAR2(12) NOT NULL, details VARCHAR2(25) ) TABLESPACE tbs1 NOLOGGING AS SELECT call_id, call_date, emp_id, call_type, details FROM calls_external; 因為 CALLS 表是真正的數(shù)據(jù)庫表，可以創(chuàng)建索引來加快訪問，表中的數(shù)據(jù)將被保留，即使外部數(shù)據(jù)文件被更新或被刪除。在建表語句中NOLOGGING關鍵字用于加快索引重建。 運用這種方法導入數(shù)據(jù)，總的導入時間為 15 秒，進程占用 CPU

15、的時間為8秒，這比前一種方法稍微慢些，但不能就此認為使用 External Table 導入數(shù)據(jù)一定比 OCI 批量插入慢。 這種方法的優(yōu)點是，未經(jīng)進行大量的編寫代碼就取得了不錯的結(jié)果，不象 OCI 批量插入存在編碼錯誤風險，它還可以使用 dbms_job 包調(diào)度數(shù)據(jù)導入進程，實現(xiàn)數(shù)據(jù)導入的自動化。其缺點是目標表必須先刪除后重建，如果只需要導入增量數(shù)據(jù)時此方法就不合適了，另外用戶在表的重建過程中訪問數(shù)據(jù)時會遇到 table or view does not exist 的錯誤，它僅適用于 Oracle 9i 以上版本的數(shù)據(jù)庫。INSERT Append as SELECT，使用 Oracle9

16、i 的 External Table 上一種方法演示了如何創(chuàng)建與外部數(shù)據(jù)文件關聯(lián)的數(shù)據(jù)庫表，其表的數(shù)據(jù)是由外部數(shù)據(jù)文件映射過來。缺點是數(shù)據(jù)庫表需要被先刪除再重建來保持與外部數(shù)據(jù)文件的一致和同步，對導入增量的數(shù)據(jù)而不需要刪除已有數(shù)據(jù)的情況不合適。針對這種需求，Oracle 提供了 INSERT 語句外帶 APPEND 提示來滿足。 INSERT /*+ APPEND */ INTO calls (call_id, call_date, emp_id, call_type, details) SELECT call_id, call_date, emp_id, call_type, details

17、 FROM calls_external;該語句讀取引用外部數(shù)據(jù)文件的 CALLS_EXTERNAL 表中內(nèi)容，并將之增加到表 CALLS 中。Append 提示告訴 Oracle 使用快速機制來插入數(shù)據(jù)，同時可以配合使用表的 NOLOGGING 關鍵字。 可以預見這種方法與前一方法消耗了相同的時間，畢竟它們是使用 External Table 特性導入數(shù)據(jù)的不同階段解決方法。如果目標表不是空的，那將會消耗稍微長的時間（因為要重建更長的索引），而前一 CREATE TABLE as SELECT 方法是整體創(chuàng)建索引。SQL*Loader的強大功能 SQL*Loader 是 Oracle 提供的

18、導入實用程序，特別針對從外部文件導入大批量數(shù)據(jù)進入數(shù)據(jù)庫表。該工具已經(jīng)有多年的歷史，每一次版本升級都使其更加強大、靈活和快捷，但遺憾的是它的語法卻是神秘而不直觀，并且只能從命令行窗口處進行調(diào)用。 盡管它有不直觀的缺點，但卻是最快最有效的導入數(shù)據(jù)方法。缺省情況下它使用 conventional path 常規(guī)選項來批量導入數(shù)據(jù)，其性能提高度并不明顯。我建議使用更快速的導入?yún)?shù)選項，在命令行添加direct=true 選項調(diào)用 direct path 導入選項。在 direct path 導入實現(xiàn)中，程序在數(shù)據(jù)庫表的新數(shù)據(jù)塊的 high water mark 處直接寫入導入數(shù)據(jù)，縮短了數(shù)據(jù)插入的處

19、理時間，同時優(yōu)化使用了非常有效的B+二叉樹方法來更新表的索引。 運用這種方法，如果使用缺省的 conventional path 導入選項，總的導入時間是 81 秒，進程占用 CPU 時間大約是 12 秒，這包括了更新表的索引時間。如果使用 direct path 導入選項，總的導入時間竟是 9 秒，進程占用 CPU 時間也僅僅是 3 秒，也包括了更新表的索引時間。 由此可見，盡管表中的索引在數(shù)據(jù)導入之前并沒有被刪除，使用SQL*Loader的direct path 導入選項仍然是快速和有效的。當然它也有缺點，就像NOLOGGING關鍵字一樣該方法不生成REDO日志數(shù)據(jù)，導入進程出錯后將無法恢

20、復到先前狀態(tài)；在數(shù)據(jù)導入過程中表的索引是不起作用的，用戶此時訪問該表時將出現(xiàn)遲緩，當然在數(shù)據(jù)導入的過程中最好不要讓用戶訪問表。分區(qū)交換 (Partition Exchange) 以上討論的數(shù)據(jù)導入方法都有一個限制，就是要求用戶在導入數(shù)據(jù)完成之后才可以訪問數(shù)據(jù)庫表。面對724不間斷訪問數(shù)據(jù)庫來說，如果我們只是導入需要增加的數(shù)據(jù)時，這種限制將對用戶的實時訪問產(chǎn)生影響。Oracle在這方面提供了表分區(qū)功能，它可以減少導入數(shù)據(jù)操作對用戶實時訪問數(shù)據(jù)的影響，操作模式就象使用可熱插拔的硬盤一樣，只不過這里的硬盤換成了分區(qū)（Partition）而已。需要聲明的是 Partitioning 分區(qū)功能只有在企業(yè)

21、版數(shù)據(jù)庫中才提供。 在一個被分區(qū)過的表中，呈現(xiàn)給用戶的表是多個分區(qū)段（segments）的集合。分區(qū)可以在需要時被添加，在維護時被卸載或刪除，分區(qū)表可以和數(shù)據(jù)庫中的表交換數(shù)據(jù)，只要它們的表結(jié)構和字段類型是一致的，交換后的分區(qū)表將擁有與之互動的表的數(shù)據(jù)。需要注意的是，這種交換只是在Oracle數(shù)據(jù)庫的數(shù)據(jù)字典層面上進行，并沒有數(shù)據(jù)被實際移動，所以分區(qū)表交換是極其快速的。 為了創(chuàng)建實驗環(huán)境，先假設CALLS表是個分區(qū)表，要創(chuàng)建一個空的分區(qū)PART_01012004，用來保存2004年1月1日的呼叫數(shù)據(jù)。然后需要再創(chuàng)建一臨時表為CALLS_TEMP，該表與CALLS表擁有相同的字段和數(shù)據(jù)類型。 我們

22、使用先前介紹的導入方法將十萬條數(shù)據(jù)導入到CALLS_TEMP表中，可以耐心等待數(shù)據(jù)完全導入到CALLS_TEMP表中，并且創(chuàng)建好索引和相關約束條件，所有這一切操作并不影響用戶實時訪問CALLS表，因為我們只對CALLS_TEMP臨時表進行了操作。一旦數(shù)據(jù)導入完成，CALLS_TEMP表就存有2004年1月1日的呼叫數(shù)據(jù)。同時利用CALLS表中名為PART_01012004的空分區(qū)，使用如下語句執(zhí)行分區(qū)交換： ALTER TABLE calls EXCHANGE PARTITION part_01012004 WITH TABLE calls_temp INCLUDING INDEXES WIT

23、HOUT VALIDATION; 分區(qū)交換操作將非?？焖俚刂桓翪ALLS表的數(shù)據(jù)字典，PART_01012004分區(qū)表即刻擁有CALLS_TEMP表的所有數(shù)據(jù)，而CALLS_TEMP表變?yōu)榭毡怼＜俣–ALLS表使用局部索引而非全局索引，上述語句中的INCLUDING INDEXES將保證分區(qū)交換包括索引的可用性，WITHOUT VALIDATION 指明不檢查交替表中數(shù)據(jù)的匹配，加快了交換的速度。結(jié)論 以上探討了Oracle數(shù)據(jù)庫的多種數(shù)據(jù)導入方法，每種方法都有其優(yōu)缺點和適用環(huán)境，能夠滿足你不同的導入需求，當然你需要在了解了這些方法后，在速度、簡易性、靈活性、可恢復性和數(shù)據(jù)可用性之間尋求最佳

24、導入方案。 為了對比各種方法的效果，我們創(chuàng)建了一個實例來展示各種方法的導入效率和效果，從中你可以選擇最適合的方法用于今后的數(shù)據(jù)導入工作。同時請記住，本文并未囊括所有的ORACLE數(shù)據(jù)導入技術（比如并行數(shù)據(jù)導入技術），這需要我們繼續(xù)不懈的探索和嘗試。 數(shù)據(jù)導入方法 總體導入時間(秒) 導入進程占用CPU時間(秒) 逐條數(shù)據(jù)插入INSERT 172 52 逐條數(shù)據(jù)插入INSERT，表暫無索引 130 35 批量插入，表暫無索引 14 7 Create As Select，使用Oracle9i的External Table 15 8 INSERT Append as SELECT，使用Oracle9

25、i的External Table 15 8 SQL*Loader conventional path 缺省導入選項 81 12 SQL*Loader direct path 導入選項 9 3 數(shù)值函數(shù)： abs(m) m的絕對值 mod(m,n) m被n除后的余數(shù) power(m,n) m的n次方 round(m,n) m四舍五入至小數(shù)點后n位的值（n缺省為0） trunc(m,n) m截斷n位小數(shù)位的值（n缺省為0） 字符函數(shù)： initcap(st) 返回st將每個單詞的首字母大寫，所有其他字母小寫 lower(st) 返回st將每個單詞的字母全部小寫 upper(st) 返回st將每個單

26、詞的字母全部大寫 concat(st1,st2) 返回st為st2接st1的末尾（可用操作符|） lpad(st1,n,st2) 返回右對齊的st,st為在st1的左邊用st2填充直至長度為n,st2的缺省為空格 rpad(st1,n,st2) 返回左對齊的st,st為在st1的右邊用st2填充直至長度為n,st2的缺省為空格 ltrim(st,set) 返回st,st為從左邊刪除set中字符直到第一個不是set中的字符。缺省時，指的是空格 rtrim(st,set) 返回st,st為從右邊刪除set中字符直到第一個不是set中的字符。缺省時，指的是空格 replace(st,search_s

27、t,replace_st) 將每次在st中出現(xiàn)的search_st用replace_st替換，返回一個st。缺省時，刪除search_st substr(st,m,n) n=返回st串的子串，從m位置開始，取n個字符長。缺省時，一直返回到st末端 length(st) 數(shù)值，返回st中的字符數(shù) instr(st1,st2,m,n) 數(shù)值，返回st1從第m字符開始，st2第n次出現(xiàn)的位置，m及n的缺省值為1 例： 1. select initcap(THOMAS),initcap(thomas) from test; initca initca - - Thomas Thomas 2. sele

28、ct concat(abc,def) first from test; first - abcdef 3. select abc| |def first from test; first - abc def 4. select lpad(name,10),rpad(name,5,*) from test; lpad(name,10) rpad(name,5,*) - - mmx mmx* abcdef abcde 5. 去掉地址字段末端的點及單詞st和rd select rtrim(address,. st rd) from test 6. select name,replace(name,a

29、,*) from test; name replace(name,a,*) - - great gre*t 7. select substr(archibald bearisol,6,9) a,substr(archibald bearisol,11) b from test; a b - - bald bear bearisol 8. select name,instr(name, ) a,instr(name, ,1,2) b from test; name a b - - - li lei 3 0 l i l 2 4 轉(zhuǎn)換函數(shù)： nvl(m,n) 如果m值為null,返回n,否則返回m

30、to_char(m,fmt) m從一個數(shù)值轉(zhuǎn)換為指定格式的字符串fmt缺省時，fmt值的寬度正好能容納所有的有效數(shù)字 to_number(st,fmt) st從字符型數(shù)據(jù)轉(zhuǎn)換成按指定格式的數(shù)值，缺省時數(shù)值格式串的大小正好為整個數(shù) 附： to_char()函數(shù)的格式： - 符號 說明 - 9 每個9代表結(jié)果中的一位數(shù)字 0 代表要顯示的先導0 $ 美元符號打印在數(shù)的左邊 L 任意的當?shù)刎泿欧?. 打印十進制的小數(shù)點 , 打印代表千分位的逗號 - 例： 1. select to_number(123.45)+to_number(234.56) form test; to_number(123.4

31、5)+to_number(234.56) - 358.01 2. select to_char(987654321) from test; to_char(987654321) - 987654321 3. select to_char(123,$9,999,999) a,to_char(54321,$9,999,999) b,to_char(9874321,$9,999,999) c from test; a b c - - - $123 $54,321 $9,874,321 4. select to_char(1234.1234,999,999.999) a,to_char(0.4567,999,999.999) b,to_char(1.1,999,999.999) from test; a b c - - - 1,234.123 .457 1.100 分組函數(shù)： avg(distinct/all n) 列n的平均值 count(all *) 返回