SQL*Loader對不同檔案及格式的處理方法

4.1大欄位(LOB型別)的匯入

    LOB作為大欄位資料型別，是Oracle新增的資料型別，用來替代long和long raw型別，一般sqlldr操作中不會涉及大欄位型別的操作，如果你遇到了這種需求，一般分以下兩種情況處理：

4.1.1資料儲存在資料檔案中

以Manager表為例，修改Remark欄位為lob型別

SCOTT@seiang11g>alter table tb_manager drop column remark;
Table altered.
SCOTT@seiang11g>alter table tb_manager add remark clob;
Table altered.

建立資料檔案和控制檔案如下：

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test12_1.dat
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16, BLAKE, HK MANAGER,"This is BLAKE.
He is a Hr Manager. The jobs responsibilities are in the following:
1.  Ensure the effective local implementation of corporate level HR initiatives and new programs.
2.  Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing......
3.  Oversee standard recruiting and procedures to ensure the conpany's staffing requirements ......
4.  Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.
5.  Develop, implement and oversee the training and development programs to upgrade the skills......"|

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test12_1.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test12_1.dat' "str '|\n'" 
TRUNCATE INTO TABLE tb_manager
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' 
(MGRNO,MNAME,JOB,REMARK char(100000))

  注意這裡REMARK顯式指定char(100000)，因為Oracle預設所有輸入欄位都是char(255)，如不顯式指定型別和長度，一旦載入列的實際長度超出255,則資料載入時自動將該行忽略，並在對應的log日誌就會報錯：Field in data file exceeds maximum length.

執行sqlldr命令，並檢視結果

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test12_1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 11:27:16 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

--檢視結果  

SCOTT@seiang11g>select * from tb_manager;

     MGRNO MNAME                JOB                            REMARK
---------- -------------------- ------------------------------ ----------------------------------------
        10 SMITH                SALES MANAGER                  This is SMITH.
                                                               He is a Sales Manager.

        11 ALLEN.W              TECH MANAGER                   This is ALLEN.W.
                                                               He is a Tech Manager.

        16 BLAKE                HK MANAGER                     This is BLAKE.
                                                               He is a Hr Manager. The jobs responsibil
                                                               ities are in the followin

資料成功載入。這種方式最關鍵的地方是必須保證REMARK列定義的長度大於資料檔案中文字塊的大小

4.1.2資料儲存在獨立的檔案中

這種資料相對於第一種更加常見，相應處理也更簡單一些(跳過了換行符的處理)，sqlldr中提供了LOBFILE關鍵字，直接支援載入檔案到LOB型別中，這實在是幫了我們大忙，下面演示一下其用法。
首先在資料庫，建立一個新表：

SCOTT@seiang11g>create table tb_lob(fileowner varchar2(30),filename varchar2(200),filesize number,filedata clob,create_date date);
Table created.

表中共有5列，分別表示檔案屬主、檔名、檔案大小、檔案內容和檔案建立時間。建資料檔案，內容如下：

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test12_2.dat
oracle  208 2017-10-31 16:11 wjq_test10.ctl
oracle  187 2017-11-01 16:18 wjq_test11_1.dat
oracle 1801 2017-11-01 16:21 wjq_test11_1.log
oracle   68 2017-11-01 10:35 wjq_test11_2.bad
oracle 1784 2017-11-01 10:40 wjq_test11_4.log
oracle  204 2017-11-01 11:30 wjq_test12_1.ctl
oracle  749 2017-11-01 11:27 wjq_test12_1.dat
oracle 1870 2017-11-01 11:29 wjq_test12_1.log
oracle  180 2017-10-31 14:41 wjq_test1.ctl
oracle 1648 2017-10-31 14:43 wjq_test1.log
oracle  163 2017-10-31 14:54 wjq_test2.ctl
oracle   82 2017-10-31 14:51 wjq_test2.dat
oracle 1650 2017-10-31 14:56 wjq_test2.log
oracle  170 2017-10-31 15:03 wjq_test3.ctl
oracle  113 2017-10-31 15:01 wjq_test3.dat
oracle 1650 2017-10-31 15:04 wjq_test3.log
oracle  186 2017-10-31 15:08 wjq_test4.ctl
oracle 1762 2017-10-31 15:08 wjq_test4.log
oracle  201 2017-10-31 15:11 wjq_test5.ctl
oracle 1777 2017-10-31 15:12 wjq_test5.log
oracle  201 2017-10-31 15:24 wjq_test6.ctl
oracle  546 2017-10-31 15:16 wjq_test6.dat
oracle 1748 2017-10-31 15:32 wjq_test6.log
oracle  155 2017-10-31 15:32 wjq_test7.ctl
oracle  404 2017-10-31 15:28 wjq_test7.dat
oracle 1748 2017-10-31 15:32 wjq_test7.log
oracle   93 2017-10-31 15:42 wjq_test8_1.dat
oracle   75 2017-10-31 15:43 wjq_test8_2.dat
oracle   20 2017-10-31 15:43 wjq_test8_3.dat
oracle  254 2017-10-31 15:45 wjq_test8.ctl
oracle 2029 2017-10-31 15:45 wjq_test8.log
oracle  425 2017-10-31 15:55 wjq_test9.ctl
oracle  354 2017-10-31 15:51 wjq_test9.dat
oracle  139 2017-10-31 15:55 wjq_test9.dsc
oracle 2766 2017-10-31 15:55 wjq_test9.log

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test12_2.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test12_2.dat'
TRUNCATE INTO TABLE tb_lob
(
FILEOWNER   position(1:6),
FILESIZE    position(*+1:11),
CREATE_DATE position(*+1:28) date 'yyyy-mm-dd hh24:mi',
FILENAME    position(*+1:45) "substr(:FILENAME,instr(:FILENAME,'/',-1)+1)",
FILEDATA    LOBFILE(FILENAME) TERMINATED BY EOF
)

  這個控制檔案是之前介紹示例應用的集大成者，又有定長處理，又有函式轉換，唯一陌生的就是最後一行:LOBFILE(FILENAME)TERMINATED BY EOF,這就是前面提到的LOBFILE 關鍵字，只需要指定FILENAME列，其他都是固定格式，呼叫時直接按此指定即可。

執行sqlldr命令，並檢視結果

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test12_2.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 12:06:28 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 47

--檢視結果  

SCOTT@seiang11g>select * from tb_lob;

FILEOWNER       FILENAME               FILESIZE FILEDATA                                           CREATE_DA
--------------- -------------------- ---------- -------------------------------------------------- ---------
oracle          wjq_test11_2.log           1782                                                    01-NOV-17
                                                SQL*Loader: Release 11.2.0.4.0 - Production on Wed
                                                 Nov 1 10:37:45 2017

                                                Copyrig

oracle          wjq_test11_3.log           1781                                                    01-NOV-17
                                                SQL*Loader: Release 11.2.0.4.0 - Production on Wed
                                                 Nov 1 10:40:46 2017

                                                Copyrig

oracle          wjq_test11_4.dat            182 10,SMITH,SALES MANAGER,This is SMITH.              01-NOV-17
                                                He is a Sales Manager.|
                                                11,ALLEN.W,TECH MA

oracle          wjq_test10.ctl              208 LOAD DATA                                          31-OCT-17
                                                INFILE '/u01/app/oracle/SQL*Loader/wjq_test10.dat'

                                                TRUNCATE INTO T

oracle          wjq_test10.dat              628 #This is data of emp                               31-OCT-17
                                                ENAME        MGR JOB             SAL
                                                ---------- ----- ---

oracle          wjq_test11_1.ctl            206 LOAD DATA                                          01-NOV-17
                                                INFILE '/u01/app/oracle/SQL*Loader/wjq_test11_1.da
                                                t'
                                                TRUNCATE INTO

oracle          wjq_test11_1.dat            187 10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sal 01-NOV-17
                                                es Manager.
                                                11,ALLEN.W,TECH
……（此處省略N行）

因為篇幅原因不全部顯示，這裡注意的一點是在dat檔案中指定的路徑下必須存在相應的檔案，即/home/oracle/sqlldr/下存在dat中指定的檔案，否則載入會報錯

4.2欄位無值導致載入報錯

    在大多數情況下，專案在實際的實施過程中，能夠順利執行的機率通常都是與專案的複雜程度成反比，越是複雜的需求，實際實施過程中出現問題的機率就越高，因此在真正實施前，是否能夠充分考慮到意外出現的情況，也是考驗實施者技術實力的一個重要方面。
  SQL*Loader工具的應用非常簡單，前面的多項示例能夠說明這一點，不過這不代表執 行SQL*Loader就不會遇到錯誤，畢竟大多數情況下SQL*Loader中極重要的一環:資料檔案的生成過程並不由你掌控，因此編寫的控制檔案是否能夠適應資料檔案中資料的複雜多樣性，就是對DBA技術實力的一項綜合考驗了。比如某天你拿到了一個這樣的資料檔案：

--資料檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test13.dat
SMITH,CLEAR,3904
ALLEN,SALESMAN, 
WARD,SALESMAN,3128
KING,PRESIDENT,2523

看起來和前面的某個例子很相似，根據此資料檔案建立控制檔案如下：

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_test13.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test13.dat'  
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY ","
(ENAME, JOB, SAL)

執行sqlldr命令，並檢視結果

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test13.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 13:47:42 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

--檢視結果  

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB                                   SAL       COMM
---------- ------------------------------ ---------- ----------
SMITH      CLEAR                                3904
WARD       SALESMAN                             3128
KING       PRESIDENT                            2523

很奇怪的發現明明4條記錄卻只匯入了3條，通過log日誌我們發現有如下提示：

Record 2: Rejected - Error on table TB_LOADER, column SAL.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table TB_LOADER:
  3 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

在此想首先闡述一個觀點：出現錯誤雖然不是什麼好訊息，但是錯誤本身並不可怕，最可怕的恰恰是沒有錯誤，程式看起來執行得好好的，四處査看也沒有提 示資訊，但就是得不到想要的正確結果，這才是最頭痛的，你想處理都無從著手。
  就本例中的錯誤資訊來說，sqlldr提示己經非常清楚：直到行結束也沒發現適當的列值。這是因為本例中資料檔案的第2行沒有提供適當的值(這一點都不稀奇，不管資料 量龐大與否，DBA絕對不能期望資料檔案完全滿足要求，因此在編寫控制檔案時，也要考慮到對意外情況的處理)
    針對這一錯誤，sqlldr甚至連解決方案也一併提供：使用TRAILING NULLCOLS。TRAILING NULLCOLS的作用是當某行沒有對應的列值時，sqlldr就會自動將其值陚為NULL，而不是報錯。
  接下來我們嘗試修改控制檔案，增加的正是sqlldr的日誌檔案中提示的，檔案修改如下所示：

[oracle@wjq SQL*Loader]$ vim wjq_test13.ctl
LOAD DATA
INFILE '/u01/app/oracle/SQL*Loader/wjq_test13.dat'  
TRUNCATE INTO TABLE tb_loader
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(ENAME, JOB, SAL)

這時候執行sqlldr命令時，結果就正常了

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
SMITH      CLEAR           3904
ALLEN      SALESMAN
WARD       SALESMAN        3128
KING       PRESIDENT       2523

通過這個例子，我們可以得到如下結論：

（1）執行完操作後一定要驗證。就本例來說，從sqlldr命令的執行來看一切正常，如果不是到SQL*Plus環境中査看匯入的資料，恐怕都不知道有記錄未被成功匯入。

（2）—定要注意看日誌，sqlldr雖然算不上智慧，但是也不傻，有時候造成錯誤 的原因只是它不知道怎麼辦好，不過日誌檔案中一定會留下處理痕跡，不管sqlldr命令執行是否成功，日誌檔案總是能告訴我們其執行的更多細節。

4.3百萬級記錄資料的載入

  前面己經講了很多示例，但資料量都較小，只能描述功能，實戰參考意義不大，因為在實際工作中應用sqlldr執行載入，多數情況資料量都達到一定級別，因此這裡 構建了一個百萬記錄級的載入，看看實際載入效率如何，以及如何進行載入優化。
  本次演示的第一小節也是資料UNLOAD的過程，只稍加修改即可以儲存為生成.csv 格式檔案的指令碼，希望也能對你有幫助。

4.3.1生成百萬資料檔案

要用到的sql程式碼如下：

--第一條sql  

[oracle@wjq SQL*Loader]$ cat getobject.sql
select a.owner||',"'||a.object_name||'",'||a.object_id||','||to_char(a.created,'yyyy-mm-dd hh24:mi:ss')|| ','||a.status from dba_objects a,(select rownum rn from dual connect by rownum<=20) b;

--第二條sql  

[oracle@wjq SQL*Loader]$ cat exe.sql
set echo off  
set term off  
set line 100 pages 0  
set feedback off  
set heading off 
spool /u01/app/oracle/SQL*Loader/wjq_object.csv  
@/u01/app/oracle/SQL*Loader/getobject.sql  
spool off  
set heading on  
set feedback on  
set term on  
set echo on

登陸到SQL*Plus中執行exe.sql

SCOTT@seiang11g>@/u01/app/oracle/SQL*Loader/exe.sql

然後用vim開啟生成的wjq_object.csv，執行:%s/\s\+$//來消除行尾的空格，生成的csv檔案107M左右

[oracle@wjq SQL*Loader]$ du -sh wjq_object.csv
107M    wjq_object.csv

4.3.2初始化環境

--建立演示表  

SCOTT@seiang11g>create table tb_objects(owner varchar2(30),object_name varchar2(50),object_id number,status varchar2(10),created date);
Table created.

--建立索引  

SCOTT@seiang11g>create index idx_wjq_obj_owner_name on tb_objects(owner,object_name); 
Index created.

4.3.3執行匯入
建立控制檔案

--控制檔案  

[oracle@wjq SQL*Loader]$ vim wjq_objects.ctl
LOAD DATA 
INFILE '/u01/app/oracle/SQL*Loader/wjq_object.csv 
TRUNCATE INTO TABLE tb_objects 
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' 
( 
 owner, 
 object_name, 
 object_id, 
 created date 'yyyy-mm-dd hh24:mi:ss', 
 status 
) 

這裡注意，對於CREATED列，我們指定了日期格式，並進行了轉換，這個格式一定要與資料檔案中日期格式相符，不然日期格式轉換時會報錯並導致資料載入失敗。
按照預設引數執行sqlldr，看看需要多長時間，同時指定ERRORS引數值為10，明確指定出現10次錯誤即中止載入。

--執行sqlldr命令  

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_objects.ctl errors=10

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 17:44:37 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
……(吃出省略N行記錄)
Commit point reached - logical record count 1740073
Commit point reached - logical record count 1740137
Commit point reached - logical record count 1740160

等待載入完成後，我們觀察對應的日誌檔案:

[oracle@wjq SQL*Loader]$ cat wjq_objects.log

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 1 17:44:37 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /u01/app/oracle/SQL*Loader/wjq_objects.ctl
Data File:      /u01/app/oracle/SQL*Loader/wjq_object.csv
  Bad File:     /u01/app/oracle/SQL*Loader/wjq_object.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 10
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TB_OBJECTS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,  O(") CHARACTER           
OBJECT_NAME                          NEXT     *   ,  O(") CHARACTER           
OBJECT_ID                            NEXT     *   ,  O(") CHARACTER           
CREATED                              NEXT     *   ,  O(") DATE yyyy-mm-dd hh24:mi:ss
STATUS                               NEXT     *   ,  O(") CHARACTER           


Table TB_OBJECTS:
  1740160 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  82560 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       1740160
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Wed Nov 01 17:44:37 2017
Run ended on Wed Nov 01 17:46:42 2017

Elapsed time was:     00:02:04.73
CPU time was:         00:00:05.14

    日誌檔案中得知，1740160條資料全部成功匯入，沒有失敗資料，共用時2分鐘左右…（因為用的是虛擬機器測試，效能可能稍微差一點）

4.3.4匯入提速

  sqlldr常規路徑匯入時預設一次載入64行，現在要載入的總行數已經達到百萬級，十位數顯然太小，我們首先嚐試修改該值，先直接在後面加個0好了，看看能對效率起到多大的提升：

  log日誌中第一句value used for ROWS parameter changed from 640 to 198，該資訊是提示由於640行所佔用的空間己經超出了引數BINDSIZE的預設值，因此自動修改到最大可承受的198行，這說明BINDSIZE引數預設值偏小，不過即使是這樣，我們看到實際執行時間也被縮短到了5分鐘，提高了一倍以上， 再進一步調整BINDSIZE引數值，預設為256K,我們將其修改為10M(1024KB*1024*10=10485760),同時將一次載入的行數提高到5000。

--執行sqlldr命令  

[oracle@wjq ~]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_objects.ctl errors=10 rows=5000 bindsize=10485760

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 2 09:11:55 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

specified value for readsize(1048576) less than bindsize(10485760)
Commit point reached - logical record count 5000
Commit point reached - logical record count 10000
Commit point reached - logical record count 15000
Commit point reached - logical record count 20000
(……此處省略N行記錄)
Commit point reached - logical record count 1734196
Commit point reached - logical record count 1739196
Commit point reached - logical record count 1740160

載入資料完成後，再次檢視日誌檔案，日誌資訊如下：

Table TB_OBJECTS:
  1740160 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                6450000 bytes(5000 rows)
Read   buffer bytes:10485760

Total logical records skipped:          0
Total logical records read:       1740160
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Thu Nov 02 09:19:27 2017
Run ended on Thu Nov 02 09:20:53 2017

Elapsed time was:     00:01:26.38
CPU time was:         00:00:04.19

  由日誌資訊可以看到，時間被縮短到1分鐘半多，幾乎提高了一倍，而此時繫結陣列才佔用了6MB左右的空間，ROWS的引數值還可以繼續提高。不過因為我們這裡記錄量和數 據量都稍小，繼續再提高這兩個引數的值，效率提升也不明顯了(僅針對這百萬記錄量的 匯入而言，如果是千萬級資料量載入，縮短的時間應該還是很明顯的)。

4.3.5使用Direct引數，讓速度更快一點

前面的引數都是基於常規路徑載入，下面通過直接路徑載入，所有引數預設，只開啟直接路徑載入引數：

--執行sqlldr命令  

[oracle@wjq ~]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_objects.ctl direct=true log=/u01/app/oracle/SQL*Loader/wjq_objects.log                          

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 2 09:31:04 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 1740160.

載入完成後，開啟log日誌，檢視節選的日誌資訊如下：

[oracle@wjq SQL*Loader]$ cat wjq_objects.log
SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 2 09:31:04 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   /u01/app/oracle/SQL*Loader/wjq_objects.ctl
Data File:      /u01/app/oracle/SQL*Loader/wjq_object.csv
  Bad File:     /u01/app/oracle/SQL*Loader/wjq_object.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table TB_OBJECTS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,  O(") CHARACTER           
OBJECT_NAME                          NEXT     *   ,  O(") CHARACTER           
OBJECT_ID                            NEXT     *   ,  O(") CHARACTER           
CREATED                              NEXT     *   ,  O(") DATE yyyy-mm-dd hh24:mi:ss
STATUS                               NEXT     *   ,  O(") CHARACTER           

The following index(es) on table TB_OBJECTS were processed:
index SCOTT.IDX_WJQ_OBJ_OWNER_NAME loaded successfully with 1740160 keys

Table TB_OBJECTS:
  1740160 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

  Date cache:
   Max Size:      1000
   Entries :       937
   Hits    :   1739223
   Misses  :         0

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       1740160
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      427
Total stream buffers loaded by SQL*Loader load thread:      262

Run began on Thu Nov 02 09:31:04 2017
Run ended on Thu Nov 02 09:31:21 2017

Elapsed time was:     00:00:16.16
CPU time was:         00:00:01.60

發現載入時間已經降到16s的時間，效能還是相當可以的

4.3.6有沒有可能更快

直接路徑匯入可用的引數也有不少，不過我們這裡總資料量不大，因此實際能夠起到效率提升的不多，我準備主要從以下兩個引數著手：
①STREAMSIZE:直接路徑載入預設讀取全部記錄，因此不需要設定ROWS引數，讀取到的資料處理後存入流快取區，即STREAMSIZE引數。該引數預設值為256KB,這裡加大到10MB。
②DATE_CACHE:該引數指定一個轉換後日期格式的快取區，以條為單位，預設值1000條，即儲存1000條轉換後的日期格式，由於我們要匯入的資料中有日期列， 因此加大該引數值到3000，以降低日期轉換操作帶來的開銷。

修改引數後執行命令最終形式如下所示：

--執行sqlldr命令  

[oracle@wjq ~]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_objects.ctl log=/u01/app/oracle/SQL*Loader/wjq_objects.log direct=true streamsize=10485760 date_cache=3000

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 2 09:37:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 1740160.

載入完成後，再次檢視命令，節選日誌資訊如下：

The following index(es) on table TB_OBJECTS were processed:
index SCOTT.IDX_WJQ_OBJ_OWNER_NAME loaded successfully with 1740160 keys

Table TB_OBJECTS:
  1740160 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

  Date cache:
   Max Size:      3000
   Entries :       937
   Hits    :   1739223
   Misses  :         0

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:10485760
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       1740160
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      427
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Thu Nov 02 09:37:56 2017
Run ended on Thu Nov 02 09:38:09 2017

Elapsed time was:     00:00:13.78
CPU time was:         00:00:01.60

170萬條的資料載入的時間大概在13秒左右，考慮到測試環境只是一臺低配的虛擬機器，這個效率也已經相當快了。

    再引申說幾句，關於優化涉及層面太多，並非單單sqlldr調整好，效率就一定最高。上述演示建立在假設資料庫層面己經最優的情況下，通過合理配置sqlldr的引數來提高 載入效率，但是不是能夠更快？我覺著回答是肯定的，優化並不是簡單地設定一個FAST=TRUE的引數，而是一個綜合考量下的結果。舉個例子，在前面例子中的控制檔案 基本都沒有指定資料型別，這樣可能會導致產生隱式的型別轉換（也影響效率），如果全部顯式指定資料型別，並且改成定長格式，匯入效率還能得到一定提升。再比如說上 述表中還建立了索引，如果單純希望資料載入效率提高，只需將表中的索引Disable，效率立刻又能提高一個數量級，但是Disable索引和約束是否符合你的業務需求，這就得看你的 實際情況了。如果你理解得足夠深刻，就會發現所謂的調優，不過是將各種因素擺在一起，取一箇中間值，保持相互平衡罷了。

小結

1.遷移、備份和恢復資料的又一個有效手段
2.不同資料庫之間進行資料遷移的非常方便而且通用的工具，避免類似EXP（EXPDP）/IMP（IMPDP）工具導致亂碼問題；
3.從文字檔案向資料庫遷移的超級有效的手段；
4.速度快，尤其結合使用直接路徑載入技術，這個技術可以跳過整個SQL引擎，同時避免undo和redo的生成，有效的提高資料的載入效率；
5.與外部表技術結合緊密。