詳解Oracle的sqlldr理論

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:這會告訴SQLLDR要做什么（在這個例子中，則指示要加載數據）。SQLLDR還可以執行CONTINUE_LOAD,也就是繼續加載。只有在繼續一個多表直接路徑加載時才能使用后面這個選項。

INFILE * :這會告訴SQLLDR所要加載的數據實際上包含在控制文件中，如第6-10行所示。也可以指定包含數據的另一個文件的文件名。如果愿意可以使用一個命令行參數覆蓋這個INFILE語句。［命令行選項會覆蓋控制文件設置］。

INTO TABLE DEPT：這告訴SQLLDR要把數據加載到哪個表中。

FILEDS TERMINATED BY ‘,’：告訴SQLLDR數據的形式應該是用逗號分隔的值。

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

BEGINDATA：告訴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 DATAINFILE *INTO TABLE DEPTREPLACE(DEPTNO position(1:2),DNAME position (3:16),LOC position (17:29))BEGINDATA10ACCOUNTIN 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）避免不必要的字符集轉換，確?？蛻舳说腘LS_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

到此這篇關于詳解Oracle的sqlldr理論的文章就介紹到這了,更多相關Oracle的sqlldr內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！