oracle10g下三种导出操作速度比较

最新推荐文章于 2021-04-05 11:44:01 发布

cqcj2023

最新推荐文章于 2021-04-05 11:44:01 发布

阅读量548

点赞数

文章标签：数据库大数据

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

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

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

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