oracle10g下三种导出操作速度比较,表T有两千万行数据,大小为2.3g。
SQL> select sum(bytes/1024/1024) from user_segments
2 where segment_name='T';
SUM(BYTES/1024/1024)
--------------------
2368
分别用exp直接路径(direct=y)导出,导出命令在
f:t.bat。命令内容为:
time /t
exp lcm/lcm tables=t direct=y file=f:t.dmp
time /t
用exp常规路径导出,导出命令在
f:tc.bat。命令内容为:
time /t
exp lcm/lcm tables=t file=f:t.dmp
time /t
用数据泵导出,导出命令在f:tdp.bat.命令内容为:
time /t
expdp lcm/lcm tables=t directory=dir dumpfile=t.dmp logfile=t.log
time /t
1。用exp直接路径(direct=y)导出,消耗时间6分钟。
C:Documents and Settingsliang>f:t.bat
C:Documents and Settingsliang>time /t
18:56
C:Documents and Settingsliang>exp lcm/lcm tables=t direct=y file=f:t.dmp
Export: Release 10.2.0.1.0 - Production on Thu Jan 18 18:56:05 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Direct Path ...
. . exporting table T 21702656 rows exported
Export terminated successfully without warnings.
C:Documents and Settingsliang>time /t
19:02
2。用exp常规路径导出,消耗时间9分钟。
C:Documents and Settingsliang>f:tc.bat
C:Documents and Settingsliang>time /t
19:05
C:Documents and Settingsliang>exp lcm/lcm tables=t file=f:t.dmp
Export: Release 10.2.0.1.0 - Production on Thu Jan 18 19:05:44 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T 21702656 rows exported
Export terminated successfully without warnings.
C:Documents and Settingsliang>time /t
19:14
3。用数据泵导出,消耗时间6分钟
C:Documents and Settingsliang>f:tdp.bat
C:Documents and Settingsliang>time /t
19:22
C:Documents and Settingsliang>expdp lcm/lcm tables=t directory=dir dumpfile=t.dmp logfile=t.log
Export: Release 10.2.0.1.0 - Production on Thursday, 18 January, 2007 19:22:19
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
Starting "LCM"."SYS_EXPORT_TABLE_01": lcm/******** tables=t directory=dir dumpfile=t.dmp logfile=t.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.312 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "LCM"."T" 1.975 GB 21702656 rows
Master table "LCM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for LCM.SYS_EXPORT_TABLE_01 is:
F:DIRT.DMP
Job "LCM"."SYS_EXPORT_TABLE_01" successfully completed at 19:28:42
C:Documents and Settingsliang>time /t
19:28
另外对于小数据量的数据表,用exp大大的快于expdp,看来expdp只适合大数据量的表。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9599/viewspace-472939/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9599/viewspace-472939/