sqluldr和ociuldr速度比较，没有明显差距，sqluldr2和exp速度差不多

〇〇

D:\>sqluldr system/sys query="select * from all_objects,(select level lv from dual connect by level<=8
)" file=all_obj.txt

       0 rows exported at 2009-03-27 13:41:42
  404576 rows exported at 2009-03-27 13:41:45
         output file all_obj.txt closed at 404576 rows.


D:\>ociuldr system/sys query="select * from all_objects,(select level lv from dual connect by level<=8
)" file=allobj.txt

       0 rows exported at 2009-03-27 13:42:02
  404576 rows exported at 2009-03-27 13:42:05
         output file allobj.txt closed at 404576 rows.

[ 本帖最后由 〇〇 于 2009-3-31 15:43 编辑 ]

suniori

ociuldr 的核心就是SQLLDR

d.c.b.a

你这种connect by的问题在于查询速度了, 数据库不快了, exp都不会快的. expdp也不会快的.

〇〇

固定宽度vs逗号分隔
D:\>ociuldr oo/oo@orcl query="select * from tcol899" file=t899.txt(60M)

       0 rows exported at 2009-03-27 16:08:12
    6000 rows exported at 2009-03-27 16:08:15
         output file t899.txt closed at 6000 rows.


D:\>ociuldr oo/oo@orcl query="select * from tcol899" file=t899f.txt(80M) field=0x20

       0 rows exported at 2009-03-27 16:08:20
    6000 rows exported at 2009-03-27 16:08:24
         output file t899f.txt closed at 6000 rows.

d.c.b.a

1. [oracle@RH4SRV1 ~]$ ./sqluldr2.bin sql=cr.sql
   
2. 
   
3.        0 rows exported at 2009-03-27 23:45:23
   
4.    14448 rows exported at 2009-03-27 23:45:24
   
5.          output file uldrdata.txt closed at 14448 rows.
   
6. 
   
7. [oracle@RH4SRV1 ~]$ ./sqluldr2.bin sql=cr.sql field=0x20
   
8. 
   
9.        0 rows exported at 2009-03-27 23:46:31
   
10.    14448 rows exported at 2009-03-27 23:46:32
    
11.          output file uldrdata.txt closed at 14448 rows.
    
12. 
    
13. [oracle@RH4SRV1 ~]$ ls -l uldrdata.txt
    
14. -rw-r--r--  1 oracle dba 25760784 Mar 27 23:46 uldrdata.txt

复制代码

一秒钟生成25MB

〇〇

我的测试是在pc台式机
e7300+2G
D:\Users\LT\Downloads>tt

D:\Users\LT\Downloads>time  0<cr
The current time is: 12:58:55.76
Enter the new time:

D:\Users\LT\Downloads>sqluldr2 oo/oo@orcl query="select * from tcol899"

       0 rows exported at 2009-03-30 12:58:56
   10000 rows exported at 2009-03-30 12:59:02
         output file uldrdata.txt closed at 10000 rows.


D:\Users\LT\Downloads>time  0<cr
The current time is: 12:59:02.34
Enter the new time:

D:\Users\LT\Downloads>ociuldr oo/oo@orcl query="select * from tcol899"

       0 rows exported at 2009-03-30 12:59:02
   10000 rows exported at 2009-03-30 12:59:08
         output file uldrdata.txt closed at 10000 rows.


D:\Users\LT\Downloads>time  0<cr
The current time is: 12:59:08.33
Enter the new time:

D:\Users\LT\Downloads>sqluldr oo/oo@orcl query="select * from tcol899"

       0 rows exported at 2009-03-30 12:59:08
   10000 rows exported at 2009-03-30 12:59:14
         output file uldrdata.txt closed at 10000 rows.


D:\Users\LT\Downloads>time  0<cr
The current time is: 12:59:14.69
Enter the new time:

D:\Users\LT\Downloads>sqluldr2 oo/oo@orcl query="select * from tcol899" field=0x20

       0 rows exported at 2009-03-30 12:59:14
   10000 rows exported at 2009-03-30 12:59:20
         output file uldrdata.txt closed at 10000 rows.


D:\Users\LT\Downloads>time  0<cr
The current time is: 12:59:21.07
Enter the new time:

D:\Users\LT\Downloads>sqluldr oo/oo@orcl query="select * from tcol899" field=0x20

       0 rows exported at 2009-03-30 12:59:21
   10000 rows exported at 2009-03-30 12:59:27
         output file uldrdata.txt closed at 10000 rows.


D:\Users\LT\Downloads>time  0<cr
The current time is: 12:59:27.20
Enter the new time:

D:\Users\LT\Downloads>ociuldr oo/oo@orcl query="select * from tcol899" field=0x20

       0 rows exported at 2009-03-30 12:59:27
   10000 rows exported at 2009-03-30 12:59:34
         output file uldrdata.txt closed at 10000 rows.


D:\Users\LT\Downloads>time  0<cr
The current time is: 12:59:34.87
Enter the new time:
D:\Users\LT\Downloads>

[ 本帖最后由 〇〇 于 2009-3-30 12:59 编辑 ]

d.c.b.a

那是你网络太慢了, exp估计也快不了

〇〇

我在服务器上测试的
把上述的@orcl去掉，测试时间不变

〇〇

D:\Users\LT\Downloads>time  0<cr
The current time is: 13:43:07.29
Enter the new time:

D:\Users\LT\Downloads>exp oo/oo tables=tcol899 file=dmp.dmp

Export: Release 10.2.0.3.0 - Production on 星期二 3月 31 13:43:07 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                         TCOL899导出了        6000 行
成功终止导出, 没有出现警告。

D:\Users\LT\Downloads>time  0<cr
The current time is: 13:43:13.02
Enter the new time:
D:\Users\LT\Downloads>tt2

D:\Users\LT\Downloads>time  0<cr
The current time is: 13:44:42.14
Enter the new time:

D:\Users\LT\Downloads>exp oo/oo tables=tcol899 file=dmp.dmp direct=y

Export: Release 10.2.0.3.0 - Production on 星期二 3月 31 13:44:42 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过直接路径...
. . 正在导出表                         TCOL899导出了        6000 行
成功终止导出, 没有出现警告。

D:\Users\LT\Downloads>time  0<cr
The current time is: 13:44:45.12
Enter the new time:
D:\Users\LT\Downloads>

〇〇

D:\Users\LT\Downloads>ex

D:\Users\LT\Downloads>time  0<cr
The current time is: 15:16:51.73
Enter the new time:

D:\Users\LT\Downloads>exp oo/oo tables=gt direct=y

Export: Release 10.2.0.3.0 - Production on 星期二 3月 31 15:16:51 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过直接路径...
. . 正在导出表                              GT导出了    51653486 行
成功终止导出, 没有出现警告。

D:\Users\LT\Downloads>time  0<cr
The current time is: 15:22:55.32
Enter the new time:

D:\Users\LT\Downloads>sqluldr oo/oo query="select * from gt"

       0 rows exported at 2009-03-31 15:22:56
  500000 rows exported at 2009-03-31 15:23:01
1000000 rows exported at 2009-03-31 15:23:07
1500000 rows exported at 2009-03-31 15:23:12
2000000 rows exported at 2009-03-31 15:23:17
2500000 rows exported at 2009-03-31 15:23:23
3000000 rows exported at 2009-03-31 15:23:28
3500000 rows exported at 2009-03-31 15:23:33
4000000 rows exported at 2009-03-31 15:23:40
4500000 rows exported at 2009-03-31 15:23:49
5000000 rows exported at 2009-03-31 15:23:55
5500000 rows exported at 2009-03-31 15:23:59
6000000 rows exported at 2009-03-31 15:24:04
6500000 rows exported at 2009-03-31 15:24:09
7000000 rows exported at 2009-03-31 15:24:15
7500000 rows exported at 2009-03-31 15:24:19
8000000 rows exported at 2009-03-31 15:24:24
8500000 rows exported at 2009-03-31 15:24:29
9000000 rows exported at 2009-03-31 15:24:36
9500000 rows exported at 2009-03-31 15:24:41
10000000 rows exported at 2009-03-31 15:24:47
10500000 rows exported at 2009-03-31 15:24:51
11000000 rows exported at 2009-03-31 15:24:56
11500000 rows exported at 2009-03-31 15:25:01
12000000 rows exported at 2009-03-31 15:25:06
12500000 rows exported at 2009-03-31 15:25:11
13000000 rows exported at 2009-03-31 15:25:16
13500000 rows exported at 2009-03-31 15:25:21
14000000 rows exported at 2009-03-31 15:25:27
14500000 rows exported at 2009-03-31 15:25:32
15000000 rows exported at 2009-03-31 15:25:37
15500000 rows exported at 2009-03-31 15:25:43
16000000 rows exported at 2009-03-31 15:25:48
16500000 rows exported at 2009-03-31 15:25:53
17000000 rows exported at 2009-03-31 15:25:58
17500000 rows exported at 2009-03-31 15:26:03
18000000 rows exported at 2009-03-31 15:26:09
18500000 rows exported at 2009-03-31 15:26:15
19000000 rows exported at 2009-03-31 15:26:20
19500000 rows exported at 2009-03-31 15:26:25
20000000 rows exported at 2009-03-31 15:26:31
20500000 rows exported at 2009-03-31 15:26:36
21000000 rows exported at 2009-03-31 15:26:44
21500000 rows exported at 2009-03-31 15:26:49
22000000 rows exported at 2009-03-31 15:26:55
22500000 rows exported at 2009-03-31 15:27:01
23000000 rows exported at 2009-03-31 15:27:05
23500000 rows exported at 2009-03-31 15:27:11
24000000 rows exported at 2009-03-31 15:27:15
24500000 rows exported at 2009-03-31 15:27:21
25000000 rows exported at 2009-03-31 15:27:26
25500000 rows exported at 2009-03-31 15:27:31
26000000 rows exported at 2009-03-31 15:27:35
26500000 rows exported at 2009-03-31 15:27:40
27000000 rows exported at 2009-03-31 15:27:45
27500000 rows exported at 2009-03-31 15:27:51
28000000 rows exported at 2009-03-31 15:27:57
28500000 rows exported at 2009-03-31 15:28:03
29000000 rows exported at 2009-03-31 15:28:09
29500000 rows exported at 2009-03-31 15:28:14
30000000 rows exported at 2009-03-31 15:28:19
30500000 rows exported at 2009-03-31 15:28:25
31000000 rows exported at 2009-03-31 15:28:30
31500000 rows exported at 2009-03-31 15:28:35
32000000 rows exported at 2009-03-31 15:28:40
32500000 rows exported at 2009-03-31 15:28:45
33000000 rows exported at 2009-03-31 15:28:51
33500000 rows exported at 2009-03-31 15:28:56
34000000 rows exported at 2009-03-31 15:29:01
34500000 rows exported at 2009-03-31 15:29:05
35000000 rows exported at 2009-03-31 15:29:11
35500000 rows exported at 2009-03-31 15:29:15
36000000 rows exported at 2009-03-31 15:29:21
36500000 rows exported at 2009-03-31 15:29:27
37000000 rows exported at 2009-03-31 15:29:33
37500000 rows exported at 2009-03-31 15:29:38
38000000 rows exported at 2009-03-31 15:29:43
38500000 rows exported at 2009-03-31 15:29:48
39000000 rows exported at 2009-03-31 15:29:53
39500000 rows exported at 2009-03-31 15:29:57
40000000 rows exported at 2009-03-31 15:30:02
40500000 rows exported at 2009-03-31 15:30:08
41000000 rows exported at 2009-03-31 15:30:13
41500000 rows exported at 2009-03-31 15:30:19
42000000 rows exported at 2009-03-31 15:30:24
42500000 rows exported at 2009-03-31 15:30:29
43000000 rows exported at 2009-03-31 15:30:34
43500000 rows exported at 2009-03-31 15:30:39
44000000 rows exported at 2009-03-31 15:30:44
44500000 rows exported at 2009-03-31 15:30:49
45000000 rows exported at 2009-03-31 15:30:53
45500000 rows exported at 2009-03-31 15:30:58
46000000 rows exported at 2009-03-31 15:31:03
46500000 rows exported at 2009-03-31 15:31:08
47000000 rows exported at 2009-03-31 15:31:13
47500000 rows exported at 2009-03-31 15:31:17
48000000 rows exported at 2009-03-31 15:31:22
48500000 rows exported at 2009-03-31 15:31:27
49000000 rows exported at 2009-03-31 15:31:33
49500000 rows exported at 2009-03-31 15:31:38
50000000 rows exported at 2009-03-31 15:31:43
50500000 rows exported at 2009-03-31 15:31:51
51000000 rows exported at 2009-03-31 15:31:55
51500000 rows exported at 2009-03-31 15:32:00
51653486 rows exported at 2009-03-31 15:32:01
         output file uldrdata.txt closed at 51653486 rows.


D:\Users\LT\Downloads>time  0<cr
The current time is: 15:32:01.82
Enter the new time:
D:\Users\LT\Downloads>dir

2009/03/31  15:32     4,700,734,086 uldrdata.txt
2009/03/31  15:22     5,422,430,208 EXPDAT.DMP