sql loader的特點
1通過網絡加載資料,意味着你可以在不同于服務端作業系統上的用戶端運作sql loader
2在同一個加載會話中加載不同的資料檔案
3在同一個加載會話中加載到不同表中
4指定資料的字元集
5有選擇的加載資料
6在加載前,使用sql操縱資料
7在特定的列生成唯一順序值
8使用操縱系統的檔案系統通路資料檔案
9從磁盤,錄音帶,或命名管道中加載
10生成精确地錯誤報告,有助于troubleshooting
11加載任意負載面向對象的資料
12對lobs和集合使用二級資料檔案
13使用正常或直接路徑加載,正常方式靈活,直接路徑方式效率高
一個典型的會話有一個輸入控制檔案,控制sql loader的行為,還有一個或多個輸入資料檔案,輸出時一個log file,bad file 和discard file
參數
參數可以放到一個檔案件,使用parfile參數調用
也可以使用options在控制檔案中指定特定參數
在指令行中的參數會覆寫參數檔案及option的參數
控制檔案
控制檔案告訴去哪找資料,怎麼解析和翻譯資料,插入到哪裡等。控制檔案有三部分組成
1第一部分包含會話級别的資訊,例如:
bindsize,rows,records to skip
infile指定輸入資料在哪裡
2第二部分包含了了更多了into table塊,每一個塊包含了要加載的資料的資訊,像是表名和表列
3第三部分是可選的,如果有,就是包含的輸入資料。
一些控制檔案的文法
1文法是自由格式的
2大小寫不敏感
3表名和列名不要包含constant和zone
4控制檔案中的注釋是兩個中橫線
輸入資料和資料檔案
sqlloader在控制檔案中指定的檔案中讀取資料,在資料檔案中的資料是以記錄的方式來組織的,一個特定的資料檔案可以使固定的記錄格式,可變的記錄格式,或是流記錄格式,記錄的格式可以再控制檔案的infile參數中指定,如果沒有記錄格式指定,預設的就是流記錄格式。
固定格式,記錄的長度都是定長的。例子
控制檔案中的内容:
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, pqrs,
0005,uvwx,
sqlldr test/test control=D:\load.ctl
SQL> select * from example;
COL1 COL2
----------- -----------
001 cd
0002 fghi
00003 lmn
1 pqrs
0005 uvwx
變長記錄,infile "datafile_name" "var n",n指定了記錄的前n個位元組是記錄的長度(長度控制數),如果n沒有被指定,預設是5位元組,n大于40會報錯,注意n是記錄的長度,分割符數量也算進來了,但是在插入的時候不會插入分隔符,terminated by辨別了字段的分割符,例子
控制檔案
load data
infile 'D:\load.dat' "var 3"
into table load
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
資料檔案
009hello,cd,010world,im
012my,name is,,
SQL> desc load;
名稱 是否為空? 類型
----------------------------------------- -------- ----------
COL1 CHAR(5)
COL2 CHAR(7)
SQL> select * from load;
COL1 COL2
----- -------
hello cd
world im
my name is
lobfiles和secondary datafiles
lob資料從logfile中加載,lob data仍然是被分域的,但是這些域不是被組織成記錄,是以處理記錄的開銷就避免了。比如,你可以從主資料檔案中讀取名字,id,然後從lobfile中讀取履歷。你也可以再lobfile中加載xml資料,可以使用xml列來儲存結構化模型資料。
secondary datafile就和主資料檔案時一樣的,包含一系列記錄,每個記錄有多個域組成,使用sdf參數指定該類型檔案,sdf可以使具體的字元串,或是一個映射的包含多個具體字元串的filler域。
資料轉換與資料類型
在正常的路徑加載中,資料域被轉換成列,有如下兩步:
1用控制檔案翻譯資料檔案的格式,解析輸入資料,操作符合insert語句的數組
2資料接收資料執行insert指令
正常路徑加載,直接路徑加載,和外部表加載
正常路徑加載,資料被拷貝到相應的綁定數組中,當數組滿了就執行insert
直接路徑加載,根據域的規格解析輸入,轉換輸入資料成列類型,構造一個列數組,列數組被傳輸到塊格式化器,在資料庫中建立資料塊,被格式化的資料直接寫到資料庫中,繞開了很多資料處理步驟,是以效率要高。
并行直接路徑加載,這個可以開多個會話并發加載資料段。
外部表,使用外部表比正常路徑加載及直接路徑加載的優勢如下:
1外部表加載資料嘗試并行加載
2外部表allows在加載資料的時候,修改資料。
外部表不能遠端加載。
sqlloader支援4中類型的lob加載
1blob,2clob,3nclob,4bfile
參數;
appned參數知道了在加載資料的時候表是非空的,如果沒有這個參數,預設是insert,sqlloader在加載的時候要求表是空表,不是空表就報錯
when指明一個或多個域條件,根據這些域條件決定是否加載資料
trailing nullcols告訴sqlloader在記錄中沒有對應的列設定成空
例如加載的資料如下
10 Accounting
假設控制檔案如下
INTO TABLE dept
TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
dname CHAR TERMINATED BY WHITESPACE,
loc CHAR TERMINATED BY WHITESPACE
)
加載後的loc這個字段就是空的。
optionally enclosed by '"',指的是雙引号内按字元串處理