某银行在用SQLLOAD向数据库中直接路径加载44G文本文件(约1亿条记录)的时候，速度非常慢，每秒只能加载10M多的数据(3W多条记录)，加载完成需要1个多小时，有时需要两个多小时，通过设置一些优化参数后，加载速度提升不明显，甚至比默认参数加载还要慢（其实加载速度并不慢，虽然每秒只能加载10M的数据感觉很慢，但是每秒加载3W多条的记录已经不慢了），除了将SQLLOAD加载改成使用SQLLOAD驱动的外部表加载应该没有什么优化办法，即使将加载速度从每秒3W多条记录提升到4W条，加载速度还是太慢。下面是将SQLLOAD加载改成SQLLOAD驱动方式的外部表做的测试。
[oracle@dm0101 hongye]$ du -sh /dbfs/my_dbfs/flatfile/20130331/EC00006D.CDU
44G /dbfs/my_dbfs/flatfile/20130331/EC00006D.CDU
使用SQLLOAD生成外部表的创建语句。
[oracle@dm0101 hongye]$ sqlldr xxx/xxx control=sqlload.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 11.2.0.3.0 - Production on Thu Nov 14 17:29:29 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
打开sqlload的log文件，找到创建DIRECTORY和建表部分，创建外部表。
SQL> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00001 AS '/home/oracle/xxx';
Directory created.
SQL> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/dbfs/my_dbfs/flatfile/20130331/';
Directory created.
SQL> CREATE TABLE "SYS_SQLLDR_X_EXT_ENMO_ASICDA"
2 (
3 "APPG_MODE" VARCHAR2(2),
4 "APPG_DATE" DATE,
5 "AS_OF_DATE" DATE,
6 "ACCT_NO" VARCHAR2(80),
7 "PARTY_ID" NUMBER,
8 "CLIT_NO" NUMBER(16),
9 "BRAN_NO" VARCHAR2(60),
10 "DEPT_ID" NUMBER(14),
11 "GL_CODE" VARCHAR2(20),
12 "GL_ACCOUNT_ID" NUMBER(14),
13 "GL_ACCOUNT_ID_DP" NUMBER(14),
14 "GL_ACCOUNT_ID_OD" NUMBER(14),
15 "GL_ACCOUNT_ID_NPL" NUMBER(14),
16 "COMMON_COA_ID" NUMBER(14),
17 "OLD_PROD_CODE" VARCHAR2(20),
18 "OLD_SUB_PROD_CODE" VARCHAR2(20),
19 "ACCT_STATUS" NUMBER(2),
20 "IS_LINK_CARD" VARCHAR2(8),
21 "CARD_NO" VARCHAR2(40),
22 "IS_CHQ" VARCHAR2(2),
23 "ISO_CURRENCY_CD" VARCHAR2(6),
24 "CUR_BOOK_BAL" NUMBER(20,2),
25 "EFFEC_BOOK_BAL" NUMBER(20,2),
26 "ACCRUAL_BASIS_CD" NUMBER(5),
27 "ADJUSTABLE_TYPE_CD" NUMBER(5),
28 "AMRT_TYPE_CD" NUMBER(5),
29 "COMPOUND_BASIS_CD" NUMBER(5),
30 "PRIME_RATE" NUMBER(13,8),
31 "CUR_NET_RATE" NUMBER(13,8),
32 "INT_TYPE" NUMBER(5),
33 "PMT_FREQ" NUMBER(5),
34 "PMT_FREQ_MULT" VARCHAR2(2),
35 "REPRICE_FREQ" NUMBER(5),
36 "REPRICE_FREQ_MULT" VARCHAR2(2),
37 "ORG_TERM" NUMBER(5),
38 "ORG_TERM_MULT" VARCHAR2(2),
39 "LAST_REPRICE_DATE" DATE,
40 "NEXT_REPRICE_DATE" DATE,
41 "ISSUE_DATE" DATE,
42 "ACCOUNT_CLOSE_DATE" DATE,
43 "ORIGINATION_DATE" DATE,
44 "MATURITY_DATE" DATE,
45 "INT_DAY_INCOME" NUMBER(24,4),
46 "INT_DAY_PAYOUT" NUMBER(24,4),
47 "INT_CACL_INCOME" NUMBER(24,4),
48 "INT_CACL_DPAYOUT" NUMBER(24,4),
49 "LOAN_LOSS_RESERVE" NUMBER(20,2),
50 "OVERDRAFT_ACCT_TYPE" NUMBER(4),
51 "OVERDRAFT_SUB_ACCT_TYPE" NUMBER(4),
52 "OVERDRAFT_STATUS" VARCHAR2(2),
53 "OVERDRAFT_START_DATE" DATE,
54 "OVERDRAFT_STOP_DATE" DATE,
55 "OVERDRAFT_LIM_AMT" NUMBER(20,2),
56 "OVERDRAFT_RATE_TYPE" VARCHAR2(2),
57 "OVERDRAFT_RATE" NUMBER(13,8),
58 "OVERDRAFT_BAL" NUMBER(20,2),
59 "DATA_SOURCE" VARCHAR2(20),
60 "INT_MON_INCOME" NUMBER(24,4),
61 "INT_MON_PAYOUT" NUMBER(24,4),
62 "CUR_BOOK_BAL_Y1" NUMBER(20,2),
63 "CUR_BOOK_BAL_Y2" NUMBER(20,2),
64 "CUR_BOOK_BAL_M1" NUMBER(20,2),
65 "CUR_BOOK_BAL_M2" NUMBER(20,2),
66 "CUR_TP_PER_ADB_M" NUMBER(20,2),
67 "PRI_TP_PER_ADB_M" NUMBER(20,2),
68 "AUTO_RENEWAL_FLG" NUMBER,
69 "LAST_MATURITY_DATE" DATE,
70 "RECORD_FLAG" VARCHAR2(2),
71 "ORIGINAL_MATURITY_DATE" DATE
72 )
73 ORGANIZATION external
74 (
75 TYPE oracle_loader
76 DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
77 ACCESS PARAMETERS
78 (
79 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
80 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00001':'EC00006D.bad'
81 LOGFILE 'sqlload.log_xt'
82 READSIZE 1048576
83 FIELDS TERMINATED BY "~|~" LDRTRIM
84 MISSING FIELD VALUES ARE NULL
85 REJECT ROWS WITH ALL NULL FIELDS
86 (
87 "APPG_MODE" CHAR(255)
88 TERMINATED BY "~|~",
89 "APPG_DATE" CHAR(255)
90 TERMINATED BY "~|~"
91 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
92 "AS_OF_DATE" CHAR(255)
93 TERMINATED BY "~|~"
94 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
95 "ACCT_NO" CHAR(255)
96 TERMINATED BY "~|~",
97 "PARTY_ID" CHAR(255)
98 TERMINATED BY "~|~",
99 "CLIT_NO" CHAR(255)
100 TERMINATED BY "~|~",
101 "BRAN_NO" CHAR(255)
102 TERMINATED BY "~|~",
103 "DEPT_ID" CHAR(255)
104 TERMINATED BY "~|~",
105 "GL_CODE" CHAR(255)
106 TERMINATED BY "~|~",
107 "GL_ACCOUNT_ID" CHAR(255)
108 TERMINATED BY "~|~",
109 "GL_ACCOUNT_ID_DP" CHAR(255)
110 TERMINATED BY "~|~",
111 "GL_ACCOUNT_ID_OD" CHAR(255)
112 TERMINATED BY "~|~",
113 "GL_ACCOUNT_ID_NPL" CHAR(255)
114 TERMINATED BY "~|~",
115 "COMMON_COA_ID" CHAR(255)
116 TERMINATED BY "~|~",
117 "OLD_PROD_CODE" CHAR(255)
118 TERMINATED BY "~|~",
119 "OLD_SUB_PROD_CODE" CHAR(255)
120 TERMINATED BY "~|~",
121 "ACCT_STATUS" CHAR(255)
122 TERMINATED BY "~|~",
123 "IS_LINK_CARD" CHAR(255)
124 TERMINATED BY "~|~",
125 "CARD_NO" CHAR(255)
126 TERMINATED BY "~|~",
127 "IS_CHQ" CHAR(255)
128 TERMINATED BY "~|~",
129 "ISO_CURRENCY_CD" CHAR(255)
130 TERMINATED BY "~|~",
131 "CUR_BOOK_BAL" CHAR(255)
132 TERMINATED BY "~|~",
133 "EFFEC_BOOK_BAL" CHAR(255)
134 TERMINATED BY "~|~",
135 "ACCRUAL_BASIS_CD" CHAR(255)
136 TERMINATED BY "~|~",
137 "ADJUSTABLE_TYPE_CD" CHAR(255)
138 TERMINATED BY "~|~",
139 "AMRT_TYPE_CD" CHAR(255)
140 TERMINATED BY "~|~",
141 "COMPOUND_BASIS_CD" CHAR(255)
142 TERMINATED BY "~|~",
143 "PRIME_RATE" CHAR(255)
144 TERMINATED BY "~|~",
145 "CUR_NET_RATE" CHAR(255)
146 TERMINATED BY "~|~",
147 "INT_TYPE" CHAR(255)
148 TERMINATED BY "~|~",
149 "PMT_FREQ" CHAR(255)
150 TERMINATED BY "~|~",
151 "PMT_FREQ_MULT" CHAR(255)
152 TERMINATED BY "~|~",
153 "REPRICE_FREQ" CHAR(255)
154 TERMINATED BY "~|~",
155 "REPRICE_FREQ_MULT" CHAR(255)
156 TERMINATED BY "~|~",
157 "ORG_TERM" CHAR(255)
158 TERMINATED BY "~|~",
159 "ORG_TERM_MULT" CHAR(255)
160 TERMINATED BY "~|~",
161 "LAST_REPRICE_DATE" CHAR(255)
162 TERMINATED BY "~|~"
163 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
164 "NEXT_REPRICE_DATE" CHAR(255)
165 TERMINATED BY "~|~"
166 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
167 "ISSUE_DATE" CHAR(255)
168 TERMINATED BY "~|~"
169 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
170 "ACCOUNT_CLOSE_DATE" CHAR(255)
171 TERMINATED BY "~|~"
172 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
173 "ORIGINATION_DATE" CHAR(255)
174 TERMINATED BY "~|~"
175 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
176 "MATURITY_DATE" CHAR(255)
177 TERMINATED BY "~|~"
178 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
179 "INT_DAY_INCOME" CHAR(255)
180 TERMINATED BY "~|~",
181 "INT_DAY_PAYOUT" CHAR(255)
182 TERMINATED BY "~|~",
183 "INT_CACL_INCOME" CHAR(255)
184 TERMINATED BY "~|~",
185 "INT_CACL_DPAYOUT" CHAR(255)
186 TERMINATED BY "~|~",
187 "LOAN_LOSS_RESERVE" CHAR(255)
188 TERMINATED BY "~|~",
189 "OVERDRAFT_ACCT_TYPE" CHAR(255)
190 TERMINATED BY "~|~",
191 "OVERDRAFT_SUB_ACCT_TYPE" CHAR(255)
192 TERMINATED BY "~|~",
193 "OVERDRAFT_STATUS" CHAR(255)
194 TERMINATED BY "~|~",
195 "OVERDRAFT_START_DATE" CHAR(255)
196 TERMINATED BY "~|~"
197 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
198 "OVERDRAFT_STOP_DATE" CHAR(255)
199 TERMINATED BY "~|~"
200 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
201 "OVERDRAFT_LIM_AMT" CHAR(255)
202 TERMINATED BY "~|~",
203 "OVERDRAFT_RATE_TYPE" CHAR(255)
204 TERMINATED BY "~|~",
205 "OVERDRAFT_RATE" CHAR(255)
206 TERMINATED BY "~|~",
207 "OVERDRAFT_BAL" CHAR(255)
208 TERMINATED BY "~|~",
209 "DATA_SOURCE" CHAR(255)
210 TERMINATED BY "~|~",
211 "INT_MON_INCOME" CHAR(255)
212 TERMINATED BY "~|~",
213 "INT_MON_PAYOUT" CHAR(255)
214 TERMINATED BY "~|~",
215 "CUR_BOOK_BAL_Y1" CHAR(255)
216 TERMINATED BY "~|~",
217 "CUR_BOOK_BAL_Y2" CHAR(255)
218 TERMINATED BY "~|~",
219 "CUR_BOOK_BAL_M1" CHAR(255)
220 TERMINATED BY "~|~",
221 "CUR_BOOK_BAL_M2" CHAR(255)
222 TERMINATED BY "~|~",
223 "CUR_TP_PER_ADB_M" CHAR(255)
224 TERMINATED BY "~|~",
225 "PRI_TP_PER_ADB_M" CHAR(255)
226 TERMINATED BY "~|~",
227 "AUTO_RENEWAL_FLG" CHAR(255)
228 TERMINATED BY "~|~",
229 "LAST_MATURITY_DATE" CHAR(255)
230 TERMINATED BY "~|~"
231 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss',
232 "RECORD_FLAG" CHAR(255)
233 TERMINATED BY "~|~",
234 "ORIGINAL_MATURITY_DATE" CHAR(255)
235 TERMINATED BY "~|~"
236 DATE_FORMAT DATE MASK 'yyyy-mm-dd hh24:mi:ss'
237 )
238 )
239 location
240 (
241 'EC00006D.CDU'
242 )
243 )REJECT LIMIT UNLIMITED;
Table created.
外部表创建完成后，使用insert into的方法将数据插入到业务表。
SQL> INSERT /*+ parallel 32 */ INTO ENMO_ASICDA
2 (
3 APPG_MODE,
4 APPG_DATE,
5 AS_OF_DATE,
6 ACCT_NO,
7 PARTY_ID,
8 CLIT_NO,
9 BRAN_NO,
10 DEPT_ID,
11 GL_CODE,
12 GL_ACCOUNT_ID,
13 GL_ACCOUNT_ID_DP,
14 GL_ACCOUNT_ID_OD,
15 GL_ACCOUNT_ID_NPL,
16 COMMON_COA_ID,
17 OLD_PROD_CODE,
18 OLD_SUB_PROD_CODE,
19 ACCT_STATUS,
20 IS_LINK_CARD,
21 CARD_NO,
22 IS_CHQ,
23 ISO_CURRENCY_CD,
24 CUR_BOOK_BAL,
25 EFFEC_BOOK_BAL,
26 ACCRUAL_BASIS_CD,
27 ADJUSTABLE_TYPE_CD,
28 AMRT_TYPE_CD,
29 COMPOUND_BASIS_CD,
30 PRIME_RATE,
31 CUR_NET_RATE,
32 INT_TYPE,
33 PMT_FREQ,
34 PMT_FREQ_MULT,
35 REPRICE_FREQ,
36 REPRICE_FREQ_MULT,
37 ORG_TERM,
38 ORG_TERM_MULT,
39 LAST_REPRICE_DATE,
40 NEXT_REPRICE_DATE,
41 ISSUE_DATE,
42 ACCOUNT_CLOSE_DATE,
43 ORIGINATION_DATE,
44 MATURITY_DATE,
45 INT_DAY_INCOME,
46 INT_DAY_PAYOUT,
47 INT_CACL_INCOME,
48 INT_CACL_DPAYOUT,
49 LOAN_LOSS_RESERVE,
50 OVERDRAFT_ACCT_TYPE,
51 OVERDRAFT_SUB_ACCT_TYPE,
52 OVERDRAFT_STATUS,
53 OVERDRAFT_START_DATE,
54 OVERDRAFT_STOP_DATE,
55 OVERDRAFT_LIM_AMT,
56 OVERDRAFT_RATE_TYPE,
57 OVERDRAFT_RATE,
58 OVERDRAFT_BAL,
59 DATA_SOURCE,
60 INT_MON_INCOME,
61 INT_MON_PAYOUT,
62 CUR_BOOK_BAL_Y1,
63 CUR_BOOK_BAL_Y2,
64 CUR_BOOK_BAL_M1,
65 CUR_BOOK_BAL_M2,
66 CUR_TP_PER_ADB_M,
67 PRI_TP_PER_ADB_M,
68 AUTO_RENEWAL_FLG,
69 LAST_MATURITY_DATE,
70 RECORD_FLAG,
71 ORIGINAL_MATURITY_DATE
72 )
73 SELECT /*+ parallel (a 32) */
74 "APPG_MODE",
75 "APPG_DATE",
76 "AS_OF_DATE",
77 "ACCT_NO",
78 "PARTY_ID",
79 "CLIT_NO",
80 "BRAN_NO",
81 "DEPT_ID",
82 "GL_CODE",
83 "GL_ACCOUNT_ID",
84 "GL_ACCOUNT_ID_DP",
85 "GL_ACCOUNT_ID_OD",
86 "GL_ACCOUNT_ID_NPL",
87 "COMMON_COA_ID",
88 "OLD_PROD_CODE",
89 "OLD_SUB_PROD_CODE",
90 "ACCT_STATUS",
91 "IS_LINK_CARD",
92 "CARD_NO",
93 "IS_CHQ",
94 "ISO_CURRENCY_CD",
95 "CUR_BOOK_BAL",
96 "EFFEC_BOOK_BAL",
97 "ACCRUAL_BASIS_CD",
98 "ADJUSTABLE_TYPE_CD",
99 "AMRT_TYPE_CD",
100 "COMPOUND_BASIS_CD",
101 "PRIME_RATE",
102 "CUR_NET_RATE",
103 "INT_TYPE",
104 "PMT_FREQ",
105 "PMT_FREQ_MULT",
106 "REPRICE_FREQ",
107 "REPRICE_FREQ_MULT",
108 "ORG_TERM",
109 "ORG_TERM_MULT",
110 "LAST_REPRICE_DATE",
111 "NEXT_REPRICE_DATE",
112 "ISSUE_DATE",
113 "ACCOUNT_CLOSE_DATE",
114 "ORIGINATION_DATE",
115 "MATURITY_DATE",
116 "INT_DAY_INCOME",
117 "INT_DAY_PAYOUT",
118 "INT_CACL_INCOME",
119 "INT_CACL_DPAYOUT",
"LOAN_LOSS_RESERVE",
121 "OVERDRAFT_ACCT_TYPE",
122 "OVERDRAFT_SUB_ACCT_TYPE",
123 "OVERDRAFT_STATUS",
124 "OVERDRAFT_START_DATE",
"OVERDRAFT_STOP_DATE",
"OVERDRAFT_LIM_AMT",
126 127 "OVERDRAFT_RATE_TYPE",
128 "OVERDRAFT_RATE",
"OVERDRAFT_BAL",
130 "DATA_SOURCE",
131 "INT_MON_INCOME",
132 "INT_MON_PAYOUT",
133 "CUR_BOOK_BAL_Y1",
134 "CUR_BOOK_BAL_Y2",
135 "CUR_BOOK_BAL_M1",
136 "CUR_BOOK_BAL_M2",
137 "CUR_TP_PER_ADB_M",
138 "PRI_TP_PER_ADB_M",
139 "AUTO_RENEWAL_FLG",
140 "LAST_MATURITY_DATE",
141 "RECORD_FLAG",
142 "ORIGINAL_MATURITY_DATE"
143 FROM "SYS_SQLLDR_X_EXT_ENMO_ASICDA" a
144 ;
83834875 rows created.
Elapsed: 00:10:19.45
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
只用了10分钟就将数据加载到数据库了，虽然还有优化空间，加载速度还可以提升，但和以前的一个小时、两个小时的加载时间比起来，已经飞快了。 如果SQLLOAD加载的数据文件是一个大文件，而不是多个小文件，如果字符编码是UTF-8或ASCII，在使用INSERT INTO SELECT的方式开并行后，ORACLE会自动分割这个SQLLOAD的数据文件，实现并行。如果这个文件是GBK编码，那么INSERT INTO SELECT的方式就无法实现并行，要使用并行就必须手动将SQLLOAD加载的数据文件分隔成多个小文件，并修改外部表的数据源。
[oracle@dm0101 20130331]$ file EC00006D.CDU
EC00006D.CDU: ASCII text, with very long lines
还好，客户的环境，SQLLOAD加载的数据文件都是ASCII编码。
本文固定链接: https://www.dbdream.com.cn/2013/11/sqlload%e5%8a%a0%e8%bd%bd%e6%95%b0%e6%8d%ae%e4%bc%98%e5%8c%96%e6%a1%88%e4%be%8b-%e4%bd%bf%e7%94%a8sqlload%e9%a9%b1%e5%8a%a8%e6%a8%a1%e5%bc%8f%e7%9a%84%e5%a4%96%e9%83%a8%e8%a1%a8/ | 信春哥，系统稳，闭眼上线不回滚！