[Oracle]-SQL LOAD介紹1

最近因為公司業務發展，原本常用的一個客戶通知函資料新增功能，使用單位希望可以有更快的方式可以解決，也因此產生了這篇SQL LOAD的文章。

客戶通知函資料新增功能簡介:
每個月公司會主動通知客戶有關公司目前的活動訊息或是會員的 生日祝賀等，新增的方式是由使用單位產生會員編號的文字檔，透過程式將文字檔讀入後，在Email內容中輸入活動訊息或是生日祝賀語，程式便會自動進行Email發送。

問題：因公司會員成長每次要匯入的名單由一開始的2-4萬筆，成長到每次匯入至少40萬筆，使用原本的方式大約需要3-5小時，程式才能處理完畢，同時該台電腦基本上已經不能處理其他事情。

使用SQL LOAD後，平均每分鐘匯入125000筆資料，40萬筆5分鐘內可匯入完畢。

【SQL-Loader 介紹】
說明：

     由使用者產生外部檔案(通常是文字檔含CSV格式)後，將外部檔案中的資料下載到資料庫

中的資料表內，下載資料的方式有很多種，你可以同時將一筆資料寫入到多個資料表中，或是

將不同的文字檔內容下載到同一個資料表中。

【SQL-Loader 檔案介紹】  

說明:

SQL-Loader 檔案主要分成下列幾種 

(1)文字檔:就是要載入的文字檔內容，欄位間可以用，做分隔也可以用|做區隔，不須表頭。

下圖為使用EXCEL產生1019-TEST.CSV的文字檔內容:

(2)控制檔:control file副檔名為*.ctl格式，主要負責提供文字檔的位置，要寫入到哪一個

          資料表(需要清楚交代欄位格式)，用哪種方式寫入，是否需要log紀錄。

控制檔範例-one.ctl：

                       LOAD DATA

                       infile 'c:\1019-TEST.csv'

                       into table one

                       fields terminated by ","

                       trailing nullcols

                       (A integer EXTERNAL,B char)
one.ctl範例檔說明：
    (a)將c:\1019-TEST.csv的cvs檔案寫入至名稱one的資料表中，A與B的欄位，其中A欄位為
         Integer 格式須加上EXTERNAL，B欄位為CHAR格式。
    (b)trailing nullcols 表示若匯入欄位時的資料為空白時，將其視為NULL。

(3)Bad File：在Load的過程中有部分資料會因為格式或資料型態等錯誤導致未能寫入的資料，就會出現在Bad File中，換言之在Bad File中的資料就是沒有被寫入的資料。
 

(4)Log File：紀錄執行過程中的紀錄，包含執行失敗時，會將失敗原因寫入，log File格式中如下圖所示。

 【SQL-Load 使用介紹】 

請在執行中輸入CMD呼交MS-DOS模式，將路經設在C:\下或是Control File所在目錄中後輸入下列指令

範例語法:

sqlldr userid/password control=one.ctl log=case1.log direct=Y

說明：

 userid為資料庫的帳號

 password為該帳號密碼

◎若要指定資料庫可以改成sqlldr userid/password@dbname方式輸入

control=表示control file的名稱，若要指定路徑可改成C:\one.ctl

log=表示使用 log file

direct=Y表示使用 direct path ; =N  使用conventional path

SQL LOADER執行畫面

 
SQL LOADER資料匯入畫面

【SQL-Load 指定文字檔長度位置匯入介紹】 

two.ctl:

LOAD DATA
INFILE 'ok.dat'
INTO TABLE EMP

( A   POSITION(01:04) INTEGER EXTERNAL,
  B   POSITION(06:15) CHAR,
  C   POSITION(17:20) DECIMAL EXTERNAL,
) 

ok.dat格式

0001 王曉明 65.5

0002 李民權 70.5

0003 丁小雨 55.3

Oracle參考資料:

http://docs.oracle.com/cd/E11882_01/server.112/e16536/ldr_concepts.htm 

其他參數介紹:

userid   --   ORACLE   username/password  control   --   Control   file   name  
log   --   Log   file   name  
bad   --   Bad   file   name  
data   --   Data   file   name  
discard   --   Discard   file   name  
discardmax   --   Number   of   discards   to   allow   (全部預設)  

skip   --   Number   of   logical   records   to   skip   (預設0)  
load   --   Number   of   logical   records   to   load   (全部預設)  
errors   --   Number   of   errors   to   allow   (預設50)  
rows   --   Number   of   rows   in   conventional   path   bind   array   or   between   direct   path   data   saves               （預設:   常规路径   64,   所有直接路径）  
bindsize   --   Size   of   conventional   path   bind   array   in   bytes(預設65536)  
silent   --   Suppress   messages   during   run   (header,feedback,errors,discards,partitions)  
direct   --   use   direct   path   (預設FALSE)  
parfile   --   parameter   file:   name   of   file   that   contains   parameter   specifications  
parallel   --   do   parallel   load   (預設FALSE)  
file   --   File   to   allocate   extents   from  
skip_unusable_indexes   --   disallow/allow   unusable   indexes   or   index   partitions(預設FALSE)  
skip_index_maintenance   --   do   not   maintain   indexes,   mark   affected   indexes   as   unusable(預設FALSE)  
  commit_discontinued   --   commit   loaded   rows   when   load   is   discontinued(預設FALSE)  
  readsize   --   Size   of   Read   buffer   (預設1048576)