zz:sqlldr理論深入學習

轉自：http://blog.csdn.net/nsj820/article/details/6744842

 

SQL*Loader是oracle的高速批量資料載入工具。這是一個非常有用的工具，可用於從多種一般影像檔案格式向Oracle資料庫中載入資料。SQLLDR可以在極短的時間內載入數量龐大的資料。它有兩種操作模式：

傳統路徑（conventional path）:SQLLDR 會利用SQL插入為我們載入資料。

直接路徑（direct path）:採用這種模式，SQLLDR不使用SQL；而是直接格式化資料庫塊，而繞過整個SQL引擎和UNDO產生，同時還可能避開REDO產生。要在一個沒有任何資料的庫中充分載入資料，最快的方法就是採用並行直接路徑載入。

常規路徑裝載使用SQL INSERT語句和記憶體中的鍵數組緩衝（bond array buffers）將資料裝載到Oracle資料庫的表中。這個過程與其他進程競爭SGA內部的記憶體資源。如果資料庫已經有支援多個並發處理進程的開銷，常規路徑裝載會降低裝載的效能。

使用常規路徑裝載的另外一個開銷是裝載進程必須搜尋資料庫，以尋找被裝載表的部分填充塊，並試圖填充這些塊。這對日常的交易處理是非常有效，但是它是常規路徑裝載的一個額外開銷。

最好或有時必須使用常規路徑裝載方法，而不能使用直接路徑裝載的情形：

1、如果被裝載的表是被索引的並且被並發訪問的，或者如果要對錶進行插入或刪除，必須使用常規路徑裝載。

2、當在控制檔案中使用SQL函數時，必須使用常規路徑裝載。當使用直接路徑裝載時，SQL函數將不適用。

3、當裝載的表是一個簇表時。

4、當裝載少量記錄到一個大型索引表，或當表具有參考完整性或檢查約束時。

5、當裝載工作是通過SQL * Net或Net8在不同的平台上進行時，為使用直接路徑裝載，兩個節點必須屬於同一個電腦家族並且使用同樣的字元集。

不需要使用SQL INSERT語句和鍵數組緩衝，直接路徑裝載格式化輸入資料到Oracle資料區塊並將它們直接寫入資料庫中。注意直接路徑裝載總是在表的最高水位之上插入資料，這種方式消除了用於搜尋部分填充塊的時間。

SQLLDR是一個命令工具，並非一個API，不能從PL/SQL調用。

SQL*Loader具有很多功能，包括以下能力：

可以從不同檔案類型的多個輸入資料檔案中載入資料；

輸入記錄可以是定長的或變長的記錄；

可以在同一次運行中載入多個表，還可以邏輯地將選定的記錄載入到每個表中；

在輸入資料載入表之前，可以對其使用SQL函數；

多個物理記錄可以被編譯成一個邏輯記錄，同樣，SQL可以提取一條物理記錄並把它作為多個邏輯記錄載入；

支援嵌套、巢狀表格、VARRAYS和LOBS（包括BLOGCLOBNLOBBFILE）。

SQL*Loader 組件：

0.控制檔案

控制檔案中包含描述輸入資料的資訊（如輸入資料的布局、資料類型等），另外還包含有關目標表的資訊，控制檔案甚至還可以包含要載入的資料。

1. SQL*Loader輸入資料：

SQL *Loader能夠接收多種不同格式的資料檔案。檔案可以儲存在磁碟或磁帶上，或記錄本身可以被嵌套到控制檔案中。記錄格式可以是定長的或變長的，定長記錄是指這樣的記錄：每條記錄具有相同的固定長度，並且每條記錄中的資料域也具有相同的固定長度、資料類型和位置

2.SQL*Loader輸出：

（1）LOAD DATA

（2）INFILE *

（3）INTO TABLE DEPT

（4）FIELDS TERMINATED BY ‘,’

（5）(DEPTNO,DNAME,LOC)

（6）BEGINDATA

（7）10,Sales,Virginia

（8）20,Accounting,Virginia

（9）30,Consulting,Virginia

（10）40,Finance,Virginia

LOAD DATA(1):這會告訴SQLLDR要做什麼（在這個例子中，則指示要載入資料）。SQLLDR還可以執行CONTINUE_LOAD,也就是繼續載入。只有在繼續一個多表直接路徑載入時才能使用後面這個選項。

INFILE * (2):這會告訴SQLLDR所要載入的資料實際上包含在控制檔案中，如第6-10行所示。也可以指定包含資料的另一個檔案的檔案名稱。如果願意可以使用一個命令列參數覆蓋這個INFILE語句。［命令列選項會覆蓋控制檔案設定］。

INTO TABLE DEPT(3)：這告訴SQLLDR要把資料載入到哪個表中。

FILEDS TERMINATED BY ‘,’(4)：告訴SQLLDR資料的形式應該是用逗號分隔的值。

（DEPTNO,DNAME,LOC）(5)：告訴SQLLDR所要載入的列，這些列在輸入資料中的順序以及資料類型。這是指輸入資料流中資料的資料類型，而不是資料庫中的資料類型，在這個例子中，列的資料類型預設為CHAR(255)。

BEGINDATA(6)：告訴SQLLDR你已經完成對輸入資料的描述，後面的行（第7-10行）是要載入到DEPT表的具體資料。

要使用以上的控制檔案，建立一個空的DEPT表：

CREATE TABLE DEPT

(DEPTNO NUMBER(2) CONSTRAINT DEPT_PK PRIMARY KEY,

DNAME VARCHAR2(14),

LOC VARCHAR2(13)

);

並運行以下命令：

Sqlldr userid=/ control=demo1.ctl

表裝載的方法：

INSERT 這是預設方法。該方法假設在資料裝載前表是空的，如果在表中有記錄，SQLLDR退出，並報：SQLLDR-601: FOR INSERT OPTION,TABLE MUST BE EMPTY,ERROR ON TABLE DEPT

APPEND這種方法允許記錄被添加到資料庫表中，而且不影響已經存在的記錄

REPLACE 這種方法首先刪除表中已經存在的記錄，然後開始裝載新的記錄。注意，當老記錄被刪除時，表上的任意刪除觸發器將被觸發

TRUNCATE 這種方法在裝載資料前，使用SQL命令TRUNCATE 刪除老的記錄，因為去除了觸發器的觸發並且沒有建立復原，所以這種方法要比REPLACE快得多。為了使用這種方法約束必須被禁止，並且要授予特定的許可權

如何載入定界資料？

定界資料，（delimited data）即用某個特殊字元分隔的資料。

Example:

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’

上面例子指定用逗號分隔資料欄位，每個欄位可以用雙引號括起。

TERMINATED BY X’9’(使用16進位格式的定位字元；採用ASCII時，定位字元為9)

TERMINATED BY WHITESPACE

如何載入固定格式資料？

通常會有一個由某個外部系統產生的一般檔案，而且這是一個定長檔案，其中包含著固定位置的資料（POSITIONAL DATA）.要載入定寬的固定位置資料，將會在控制檔案中使用POSITION關鍵字：

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

(DEPTNO position(1:2),

DNAME position (3:16),

LOC position (17:29)

)

BEGINDATA

10ACCOUNTIN Virginia ,USA

可以使用相對位移量。

DEPTNO position(1:2),

DNAME position(*:16)

表示DNAME 從3-16個字元。

如何載入日期？

只需要控制檔案中DATE資料類型，並指定要使用的日期掩碼。這個日期掩碼與資料庫中的TO_CHAR和TO_DATE中使用的日期掩碼是一樣。

如何使用函數載入資料？

只需要將函數加到控制檔案中

（DNAME “UPPER(:dname)”

）

TRAILING NULLCOLS 會導致綁定變數成為NULL,如果輸入記錄中不存在某一列的資料，SQLLDR會為該資料行繫結一個NULL值。

下面是增加SQL*Loader效能的一些補充技巧：

1）使用定位域而不要使用分隔域，分隔域要求裝載器搜尋資料以尋找分隔字元。定位域比較快，因為裝載器只需要做簡單的指標運算。

2）為終止域指定最大長度，使每個捆綁數組更為有效地插入。

3）預分配足夠的儲存空間。當資料被裝載時，表中需要更多的空間， Oracle分配更多的區間以容納資料，如果在資料裝載期間頻繁地做這項操作，處理的開銷將非常大。在裝載之前計算或估算儲存空間需求能夠讓你預先建立必要的儲存空間。

4）如果可能，在控制檔案中盡量避免使用NULLIF和DEFAULTIF子句。這兩個子句對於被裝載的每條記錄都會引起列運算。

5）分割資料檔案，並行運行常規路徑裝載。

6）通過使用命令列參數ROWS，減少提交次數。

7）避免不必要的字元集轉換，確保用戶端的NLS_LANG環境與伺服器端的相同。

8）只要可能，盡量使用直接路徑裝載方法。

9）當使用直接路徑裝載方法時，為表的最大索引預先排序並使用SORTED INDEXES子句。

10）當使用直接路徑裝載方法時，盡量使用並行直接路徑選項。

11）在直接路徑裝載期間，儘可能少使用重做日誌。有三種不同層級的控制實現這點：

禁止資料庫歸檔；

在控制檔案中使用關鍵字UNRECOVERABLE；

使用NOLOG屬性修改表和/或索引。

直接路徑並行載入的格式範例：

/opt/app/oracle/product/10.2.0/bin/sqlldr APS/APS control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTLdirect=true
parallel=true    LOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.log bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad rows=10000 readsize=20000000 bindsize=20000000  DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis