1.工具介绍

dmfldr工具类似于Oracle SQLLDR工具 ，是一款数据快速装载工具
用法
dmfldr USERID=SYSDBA/SYSDBA CONTROL=‘tmp/fldr.ctl’
ctl文件语法

[OPTIONS(

<id>=<value>

……

  	)]

LOAD [DATA]

INFILE < <file_option>|<directory_option> >

[BADFILE <path_name>]

[APPEND|REPLACE|INSERT]

<into_table_clause>

<id> ::=参数

<value> ::=值

<file_option> ::= [LIST] <path_name> [<row_term_option>] [,<path_name>
[<row_term_option>]]

<directory_option> ::= DIRECTORY <path_name> [<row_term_option>]

<path_name> ::=文件地址

<row_term_option> ::=STR [X] <delimiter>

<into_table_clause> ::= <into_table_single>{<into_table_single>}

<into_table_single> ::=INTO TABLE [<schema>.]<tablename>

  					[EP <ep_option>]

  					[WHEN <field_conditions>]

  					[FIELDS [TERMINATED BY] [X] <delimiter>]

  					[<enclosed_option>]

  					[<coldef_option>]

<schema> ::=模式名

<tablename> ::=表名

<ep_option> ::=(<ep_list>)

<ep_list> ::=整型数字列表，以逗号分隔

<field_conditions> ::= <field_condition>{ AND <field_condition>}

<field_condition> ::= [(] <cmp_exp><cmp_ops><cmp_data>[)]

<cmp_exp> ::= <colid> | (p1:p2)

<cmp_ops> ::= = | <> | !=

<cmp_data> ::= [X] '<字符串常量>' | BLANKS | WHITESPACE

<delimiter> ::='<字符串常量>'

<coldef_option> ::=(<col_def>{ ,<col_def>})

<col_def>::=<col_id> [FILLER][<property_option>][<fmt_option>][<term_option>]  [<enclosed_option>][<constant_option>][<fun_option>]

<col_id> ::=列名

<property_option> ::=<position_option> | NULL

<position_option> ::=position(p1:p2) | position(p1)

<fmt_option> ::=DATE FORMAT '<时间日期格式串>'

<term_option> ::= TERMINATED [BY] <wx_option>

<wx_option> ::= WHITESPACE|[X] <delimiter>

<enclosed_option> ::= [OPTIONALLY] ENCLOSE [BY] [X] <delimiter>

<constant_option> ::= CONSTANT "<常量>"

<fun_option> ::= "函数名称()"

2.构造测试数据

SQL> declare 
2   begin
3   for i in 1..10000000 loop
4   insert into dm_test_info values(i,'dm_02','DMDSC,DMMPP,DMDW');
5   if mod(i,10000)=0 then
6   commit;
7   end if;
8   end loop;
9   end;
10  /
DMSQL executed successfully
used time: 00:01:40.379. Execute id is 99601.


set lineshow off;
set heading off;

spool 1.txt
select id||'|'||name||'|'||dm_name from dm_test_info;
spool off

[dmdba@dm8 bin]$ du -sh 1.txt 
832M	1.txt
[dmdba@dm8 bin]$ 

3.开始导入

truncate table dm_test_info;

构造控制文件

LOAD DATA

INFILE '/dm8/dmdbms/bin/1.txt'

BADFILE '/tmp/dm_test_info.bad'

INTO TABLE dm.dm_test_info

FIELDS '|'
(
id,
name,
dm_name
)


[dmdba@dm8 bin]$ time /dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/home/dmdba/dmfldr_dm_test_info.ctl\'
dmfldr V8
Control file: 

Loaded rows: All

Rows per commit to server: 50000

Rows to skip: 0

Errors count allowed: 100

Whether to load direct: Yes

Whether to insert identity column: No

Whether data is sorted by cluster index: No

Character sets:GBK



Data file counts: 1
/dm8/dmdbms/bin/1.txt

Error file :/tmp/dm_test_info.bad

Dest table :DM.DM_TEST_INFO

Column Name                                                                                                                      Packed data type     End
ID                                                                                                                               CHARACTER            |
NAME                                                                                                                             CHARACTER            |
DM_NAME                                                                                                                          CHARACTER            |

row buffer number is: 2
task thread number is: 1

100000 rows committed
200000 rows committed
300000 rows committed
400000 rows committed
500000 rows committed
600000 rows committed
700000 rows committed
800000 rows committed
900000 rows committed
1000000 rows committed
1100000 rows committed
1200000 rows committed
1300000 rows committed
1400000 rows committed
1500000 rows committed
1600000 rows committed
1700000 rows committed
1800000 rows committed
1900000 rows committed
2000000 rows committed
2100000 rows committed
2200000 rows committed
2300000 rows committed
2400000 rows committed
2500000 rows committed
2600000 rows committed
2700000 rows committed
2800000 rows committed
2900000 rows committed
3000000 rows committed
3100000 rows committed
3200000 rows committed
3300000 rows committed
3400000 rows committed
3500000 rows committed
3600000 rows committed
3700000 rows committed
3800000 rows committed
3900000 rows committed
4000000 rows committed
4100000 rows committed
4200000 rows committed
4300000 rows committed
4400000 rows committed
4500000 rows committed
4600000 rows committed
4700000 rows committed
4800000 rows committed
4900000 rows committed
5000000 rows committed
5100000 rows committed
5200000 rows committed
5300000 rows committed
5400000 rows committed
5500000 rows committed
5600000 rows committed
5700000 rows committed
5800000 rows committed
5900000 rows committed
6000000 rows committed
6100000 rows committed
6200000 rows committed
6300000 rows committed
6400000 rows committed
6500000 rows committed
6600000 rows committed
6700000 rows committed
6800000 rows committed
6900000 rows committed
7000000 rows committed
7100000 rows committed
7200000 rows committed
7300000 rows committed
7400000 rows committed
7500000 rows committed
7600000 rows committed
7700000 rows committed
7800000 rows committed
7900000 rows committed
8000000 rows committed
8100000 rows committed
8200000 rows committed
8300000 rows committed
8400000 rows committed
8500000 rows committed
8600000 rows committed
8700000 rows committed
8800000 rows committed
8900000 rows committed
9000000 rows committed
9100000 rows committed
9200000 rows committed
9300000 rows committed
9400000 rows committed
9500000 rows committed
9600000 rows committed
9700000 rows committed
9800000 rows committed
9900000 rows committed
10000000 rows committed

Dest table :DM.DM_TEST_INFO
load success.
10000000 rows loaded success.
0 rows not loaded due to data format error.
0 rows not loaded due to data error.

Skip logic record counts: 0
Read logic record counts: 10000000
Refuse logic record counts: 0

41442.999(ms) time used.


real	0m41.651s
user	0m13.651s
sys	0m0.505s

导入1000w数据需要41s，用sqlldr的方式在Oracle导入大概用时3分47s

达梦技术社区:https://eco.dameng.com