SQL*Loader是oracle的高速批量數(shù)據加載工具。這是一個非常有用的工具，可用于從多種平面文件格式向Oracle數(shù)據庫中加載數(shù)據。SQLLDR可以在極短的時間內加載數(shù)量龐大的數(shù)據。

它有 兩種操作模式：

傳統(tǒng)路徑（conventional path）:SQLLDR 會利用SQL插入為我們加載數(shù)據。

直接路徑（direct path）:采用這種模式，SQLLDR不使用SQL；而是直接格式化數(shù)據庫塊，而繞過整個SQL引擎和UNDO生成，同時還可能避開REDO生成。要在一個沒有任何數(shù)據的庫中充分加載數(shù)據，最快的方法就是采用并行直接路徑加載。

常規(guī)路徑裝載使用SQL INSERT語句和內存中的鍵數(shù)組緩存（bond array buffers）將數(shù)據裝載到Oracle數(shù)據庫的表中。

這個過程與其他進程競爭SGA內部的內存資源。如果數(shù)據庫已經有支持多個并發(fā)處理進程的開銷，常規(guī)路徑裝載會降低裝載的性能。

使用常規(guī)路徑裝載的另外一個開銷是裝載進程必須搜索數(shù)據庫，以查找被裝載表的部分填充塊，并試圖填充這些塊。這對日常的事務處理是非常有效的，但是它是常規(guī)路徑裝載的一個額外開銷。

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

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

2、當在控制文件中使用SQL函數(shù)時，必須使用常規(guī)路徑裝載。當使用直接路徑裝載時，SQL函數(shù)將不適用。

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

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

5、當裝載工作是通過SQL * Net或Net8在不同的平臺上進行時，為使用直接路徑裝載，兩個節(jié)點必須屬于同一個計算機家族并且使用同樣的字符集。

不需要使用SQL INSERT語句和鍵數(shù)組緩存，直接路徑裝載格式化輸入數(shù)據到Oracle數(shù)據塊并將它們直接寫入數(shù)據庫中。注意直接路徑裝載總是在表的最高水位之上插入數(shù)據，這種方式消除了用于搜索部分填充塊的時間。

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

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

可以從不同文件類型的多個輸入數(shù)據文件中加載數(shù)據；

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

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

在輸入數(shù)據載入表之前，可以對其使用SQL函數(shù)；

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

支持嵌套、嵌套表、VARRAYS和LOBS（包括BLOGCLOBNLOBBFILE）。

SQL*Loader 組件：

0.控制文件

控制文件中包含描述輸入數(shù)據的信息（如輸入數(shù)據的布局、數(shù)據類型等），另外還包含有關目標表的信息，控制文件甚至還可以包含要加載的數(shù)據。

1. SQL*Loader輸入數(shù)據：

SQL *Loader能夠接收多種不同格式的數(shù)據文件。文件可以存儲在磁盤或磁帶上，或記錄本身可以被嵌套到控制文件中。記錄格式可以是定長的或變長的，定長記錄是指這樣的記錄：每條記錄具有相同的固定長度，并且每條記錄中的數(shù)據域也具有相同的固定長度、數(shù)據類型和位置

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

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

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

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

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

BEGINDATA：告訴SQLLDR你已經完成對輸入數(shù)據的描述，后面的行（第7-10行）是要加載到DEPT表的具體數(shù)據。

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

CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT DEPT_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

并運行以下命令：

Sqlldr userid=/ control=demo1.ctl

表裝載的方法：

INSERT 這是缺省方法。該方法假設在數(shù)據裝載前表是空的，如果在表中有記錄，SQLLDR退出，并報：SQLLDR-601: FOR INSERT OPTION,TABLE MUST BE EMPTY,ERROR ON TABLE DEPT

APPEND這種方法允許記錄被添加到數(shù)據庫表中，而且不影響已經存在的記錄

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

TRUNCATE 這種方法在裝載數(shù)據前，使用SQL命令TRUNCATE 刪除老的記錄，因為去除了觸發(fā)器的觸發(fā)并且沒有創(chuàng)建回滾，所以這種方法要比REPLACE快得多。為了使用這種方法約束必須被禁止，并且要授予特定的權限

如何加載定界數(shù)據？

定界數(shù)據，（delimited data）即用某個特殊字符分隔的數(shù)據。

Example:

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

上面例子指定用逗號分隔數(shù)據字段，每個字段可以用雙引號括起。

TERMINATED BY X’9’(使用16進制格式的制表符；采用ASCII時，制表符為9)

TERMINATED BY WHITESPACE

如何加載固定格式數(shù)據？

通常會有一個由某個外部系統(tǒng)生成的平面文件，而且這是一個定長文件，其中包含著固定位置的數(shù)據（POSITIONAL DATA）.要加載定寬的固定位置數(shù)據，將會在控制文件中使用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數(shù)據類型，并指定要使用的日期掩碼。這個日期掩碼與數(shù)據庫中的TO_CHAR和TO_DATE中使用的日期掩碼是一樣。

如何使用函數(shù)加載數(shù)據？

只需要將函數(shù)加到控制文件中

（DNAME “UPPER(:dname)”）

TRAILING NULLCOLS 會導致綁定變量成為NULL,如果輸入記錄中不存在某一列的數(shù)據，SQLLDR會為該列綁定一個NULL值。

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

1）使用定位域而不要使用分隔域，分隔域要求裝載器搜索數(shù)據以查找分隔符。定位域比較快，因為裝載器只需要做簡單的指針運算。

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

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

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

5）分割數(shù)據文件，并行運行常規(guī)路徑裝載。

6）通過使用命令行參數(shù)ROWS，減少提交次數(shù)。

7）避免不必要的字符集轉換，確?？蛻舳说腘LS_LANG環(huán)境與服務器端的相同。

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

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

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

11）在直接路徑裝載期間，盡可能少使用重做日志。有三種不同級別的控制實現(xiàn)這點：

禁止數(shù)據庫歸檔；

在控制文件中使用關鍵字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內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論