SQL*Loader 常用知識

perfychi發表於2013-06-14
SQL
http://blog.chinaunix.net/uid-23284114-id-3259752.html

日常中，大量有格式的資料存放在平面檔案中，如csv、txt中。通過sql*loader可以很方便匯入到oracle資料庫中。

先看一個例子，把平面檔案test_action.txt中的資料匯入到test_action表中。
test_action.txt
--------
"2012-06-25 00:03:01","5D8969C289594C4FE76188066C4D72C4","official","newwap","boutique_product","0","140","x39x0xxx89x3x","S60V52","45"
--------

test_action表結構
SQL*Loader 常用知識

控制檔案
test_action.ctl
----
load data
CHARACTERSET UTF8
infile '/home/oracle/test_action.txt'
append into table scott.test_p2sp
fields terminated by ","
optionally enclosed by '"'
(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )
----

匯入語句
$ sqllder user/password errors=1000 control='/home/oracle/test_action.ctl' 


一、sql*loader的組成
SQL*Loader 常用知識

1.命令
sqlldr user/password errors=n control='/xx/xx/xx/xx.ctl'  log='/xx/xx.log' bad='/xx/xx.bad'
--errors：允許失敗的最大行數，預設50。超過errors sqlldr自動停止。
--control：控制檔案
--log：日誌，不指定也可，預設在執行sqlldr命令所在的目錄。
--bad：記錄匯入失敗的資料，預設在執行sqlldr命令所在的目錄。


2.平面檔案
平面檔案中存放有規律的資料，比如以","逗號作為列之間的分割，各列以雙引號""包圍。
如下：
"2012-06-25 00:03:01","5D8969C289594C4FE76188066C4D72C4","official","newwap","boutique_product","0","140","x39x0xxx89x3x","S60V52","45"


3.控制檔案.ctl
也就是上面命令中的control=。控制檔案時平面檔案與資料庫表之間的橋樑。控制檔案指定平面檔案各列對應表中的哪些列。
如:
load data
CHARACTERSET UTF8
infile '/home/oracle/test_action.txt'
append into table scott.test_p2sp
fields terminated by ","
optionally enclosed by '"'
(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )

二、常用引數
sqlldr help=y可以檢視所有引數

DIRECT:直接路徑載入，它直接將資料寫入到database block中，大大加速了匯入速度。預設值是false。使用direct=true，表中的觸發器失效。 
PARALLEL:並行載入。預設值是false。
rows:多少行提交一次
bindsize: 每次提交記錄(依據rows引數)的緩衝區的大小，預設255420 bytes，一般設定為20971520(20M)，目前還沒測出最大值。與rows配合使用。在conventional path load（一般方式)下有效。bindsize制約rows，如果rows提交的資料，
即 行*行長>bindsize，那麼還是以bindsize為準。所以增大rows，一定要考慮bindsize是否夠用。
readsize:讀取緩衝區的大小 (預設 1048576),一般設定成20971520(20M)。readsize負責讀，而bindsize負責提交。如果readsize小於bindsize，那麼readsize會自動增加。


三、加速匯入
1.表改為nologging
執行sqlldr命令之前，最好把相關表的logging模式改為nologging，這樣會減少redo日誌產生，同時也就加速了匯入的速度。導完後再改為logging模式。
alter table table_name nologging;
alter table table_name logging;

2.PARALLEL
在命令中加入parallel=true
並行技術

3.DIRECT
使用direct path load(直接路徑載入)，觸發器會失效。
有效的約束有：
NOT NULL
UNIQUE
PRIMARY KEY (unique-constraints on not-null columns)
失效的約束：
CHECK constraints
Referential constraints (FOREIGN KEY)

當然，也可以parallel=true+direct=true

4.rows
rows=n，匯入n行提交一次。

5.bindsize:
和rows結合使用，決定一次提交的行數。

6.readsize



四、控制檔案引數
load data
CHARACTERSET UTF8
infile '/home/oracle/test_action.txt'
append into table scott.test_p2sp
fields terminated by ","
optionally enclosed by '"'
(LOG_TIME date "yyyy-mm-dd hh24:mi:ss",MD5ID,CHANNEL,SUB_CHANNEL,ACTION_TYPE,STATUS,VER,IMEI,MBL,PRODUCT_ID )


以上面為例：
1.CHARACTERSET :字符集,如UTF8 
infile 平面檔案所在位置，這裡可以寫多個infile，也就是多個平面檔案匯入到一張表裡
infile '/home/oracle/1.csv'
infile '/home/oracle/2.csv'

2.xx into table:append into --追加
                  truncate into --把表的資料都刪除，然後匯入
                  insert into --空表可以採用insert

3.fields terminated by "," :以逗號作為分隔
4.optionally enclosed by '"':列被雙引號包圍

5.函式
（1）日期轉換：平面檔案中
"2012-06-25 00:03:01",
那麼ctl中，要根據平面檔案日期格式在ctl中標明
LOG_TIME date "yyyy-mm-dd hh24:mi:ss",

（2）upper，轉成大寫
格式:col1 "upper(:col1)"
如：ACTION_TYPE "upper(:action_type)"


如果表中某列的型別為varchar2(300),那麼在ctl中就要寫成char(300)

SQLLDR預設輸入流為資料型別為CHAR 長度為 255 所以當 begindata section裡面的 輸入流 長於255 的時候會報錯的


五、sqllder的兩種方式
Direct path load和conventional path load。
從這裡可以明顯看出Direct path load 與（常用方式）的區別
SQL*Loader 常用知識
1.Conventional Path Load
使用sql insert方式插入資料。
特點：
（1）適用表中含有索引
（2）適用簇表：load data to a clustered table。direct path load不支援load data to  a clustered table簇表。
（3）滿足所有約束constraint，如索引、not null等。
（5）你想知道哪些資料被rejected，資訊記錄在sqlldr的log和bad檔案中。
（6）觸發表中觸發器
（7）在插入時，別的使用者可以操作該表

2.Direct Path Load
直 接把資料寫到database block中，效率比Conventional Path Load高得多。Direct Path Load不經過oracle的sql這一層，也沒有parseing、executing這些過程，對於oracle資料庫的整個負載都是一個減輕。插入 資料時，只是在表的HWM之上直接重新分配新的資料塊，而不是在freelist中查詢可以insert 的塊。
注意：direct path load方式也會產生redo，因為插入資料時會分配新extend，oracle必須記錄這些redo資訊。
特點：
（1）direct path load不是滿足所有的constraint，只滿足primary key、unique、not null
（2）不能插入cluster table
（3）direct path load，在插入時，別的使用者不能操作該表（不能進行DML操作，不能建立、重建索引，甚至查詢）
（4）不會觸發觸發器
（5）適用於將大量資料匯入到空表或者非空表，這個表最好不要含有觸發器、參考約束。




Conventional Path Load（傳統匯入）優化實驗：
1.rows=10000,10000行提交一次
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000
##Space allocated for bind array:                 255420 bytes(99 rows)
##Elapsed time was:     00:03:05.90
#這裡rows=10000,而實際是99 rows提交一次，原因在於bindsize

2.rows=10000,10000行提交一次，加大bindsize
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 bindsize=20971520
##Space allocated for bind array:               20970240 bytes(8128 rows) //依然bindsize
##Elapsed time was:    // 00:00:52.51 效率大大提高
#提示specified value for readsize(1048576) less than bindsize(20971520)
解決辦法：可以忽略，或者設定readsize和bindsize等值（意義不大，當readsize

3.rows=10000,10000行提交一次，繼續加大bindsize
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 bindsize=25800000 
##Space allocated for bind array:               25800000 bytes(10000 rows)//終於可以一次提交10000行資料了
##Elapsed time was:     00:00:50.37  // 增大bindsize後，效果有所提高

4.row=10000,10000行提交一次,新增readsize
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/p2sp_action.ctl' rows=10000 readsize=25800000 bindsize=25800000 
##Space allocated for bind array:               25800000 bytes(10000 rows)
##Elapsed time was:     00:00:49.49 //新增readsize引數後，效率有所提高

5.rows=10000,10000行提交一次，新增parallel=true
$ sqlldr scott/tiger errors=1000000 control='/home/oracle/test_action.ctl' rows=10000 readsize=25800000 bindsize=25800000  parallel=true
##Space allocated for bind array:               25800000 bytes(10000 rows)
##Elapsed time was:     00:00:48.86 //效率又提高了

總結：rows+bindsize+readsize是傳統匯入最優方案。


Direct Path load實驗：
$ sqlldr scott/tiger@67 errors=1000000 control='/home/oracle/test_action.ctl' direct=true 
##Elapsed time was:     00:00:06.54//速度驚人啊！



遠端sqlldr匯入資料
在A伺服器上有csv檔案，要匯入到B庫中



用sqlldr+tnsnames.ora就可以實現

1.首先要在A伺服器安裝oracle client，client版本最好和DB版本相同
如果A伺服器沒有oracle DB，那麼安裝oracle client來獲得sqlldr命令，安裝的時候選擇自定義安裝，選擇oracle database utilties和oracle net兩項即可。

2.A庫配置tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/product/11.1.0/client/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
B =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.67)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = hou)
    )
  )


3.A伺服器上執行sqlldr命令，csv資料和ctl控制檔案都在A伺服器
$ sqlldr scott/tiger@67 errors=10 control='/home/oracle/test.ctl'

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27042095/viewspace-763972/，如需轉載，請註明出處，否則將追究法律責任。

相關文章