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

它有 兩種操作模式：

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

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

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

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

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

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

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

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

3、當(dāng)裝載的表是一個(gè)簇表時(shí)。

4、當(dāng)裝載少量記錄到一個(gè)大型索引表，或當(dāng)表具有引用完整性或檢查約束時(shí)。

5、當(dāng)裝載工作是通過SQL * Net或Net8在不同的平臺(tái)上進(jìn)行時(shí)，為使用直接路徑裝載，兩個(gè)節(jié)點(diǎn)必須屬于同一個(gè)計(jì)算機(jī)家族并且使用同樣的字符集。

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

SQLLDR是一個(gè)命令工具，并非一個(gè)API，不能從PL/SQL調(diào)用。

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

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

輸入記錄可以是定長(zhǎng)的或變長(zhǎng)的記錄；

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

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

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

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

SQL*Loader 組件：

0.控制文件

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

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

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

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

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

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

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

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

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

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

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

并運(yùn)行以下命令：

Sqlldr userid=/ control=demo1.ctl

表裝載的方法：

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

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

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

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

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

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

Example:

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

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

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

TERMINATED BY WHITESPACE

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

通常會(huì)有一個(gè)由某個(gè)外部系統(tǒng)生成的平面文件，而且這是一個(gè)定長(zhǎng)文件，其中包含著固定位置的數(shù)據(jù)（POSITIONAL DATA）.要加載定寬的固定位置數(shù)據(jù)，將會(huì)在控制文件中使用POSITION關(guān)鍵字：

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position(1:2),
DNAME position (3:16),
LOC position (17:29)
)
BEGINDATA
10ACCOUNTIN Virginia ,USA

可以使用相對(duì)偏移量。

DEPTNO position(1:2),
DNAME position(*:16)

表示DNAME 從3-16個(gè)字符。

如何加載日期？

只需要控制文件中DATE數(shù)據(jù)類型，并指定要使用的日期掩碼。這個(gè)日期掩碼與數(shù)據(jù)庫(kù)中的TO_CHAR和TO_DATE中使用的日期掩碼是一樣。

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

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

（DNAME “UPPER(:dname)”）

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

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

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

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

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

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

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

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

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

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

9）當(dāng)使用直接路徑裝載方法時(shí)，為表的最大索引預(yù)先排序并使用SORTED INDEXES子句。

10）當(dāng)使用直接路徑裝載方法時(shí)，盡量使用并行直接路徑選項(xiàng)。

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

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

在控制文件中使用關(guān)鍵字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

到此這篇關(guān)于詳解Oracle的sqlldr理論的文章就介紹到這了,更多相關(guān)Oracle的sqlldr內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論