Export provides two methods for exporting table data:
-
Conventional path Export
-
Direct path Export
Conventional path Export uses the SQL SELECT
statement
to extract data from tables. Data is read from disk into a buffer cache,
and rows are transferred to the evaluating buffer. The data, after
passing expression evaluation, is transferred to the Export client,
which then writes the data into the export file.
Direct path Export is much faster than conventional path Export
because data is read from disk into the buffer cache and rows are
transferred directly to the Export client.
The evaluating buffer (that is, the SQL command-processing layer) is
bypassed. The data is already in the format that Export expects, thus
avoiding unnecessary data conversion. The data is transferred to the
Export client, which then writes the data into the export file.
大意就是常规路径导出是使用SELECT的方式查询数据库中的数据,是需要通过buffer cache并通过sql的语句处理层再转移至export的导出文件,直接路径则是直接从磁盘上将数据转移至export的导出文件,所以速度更快。
两种方式导出数据的时间比较:
1、常规路径
expPFILE的内容:
FILE=fulldata
FULL=y
STATISTICS=none
LOG=explog
$time exp scott/tiger PARFILE=expPFILE buffer=5000000
Export: Release 10.2.0.4.0 - Production on Mon Apr 19 16:03:16 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
。。。。。。。
. exporting statistics
Export terminated successfully with warnings.
real 2m0.969s
user 0m11.222s
sys 0m11.259s
2、直接路径
expPFILE的内容:
FILE=fulldata
FULL=y
STATISTICS=none
LOG=explog
DIRECT=y
$ time exp scott/tiger PARFILE=expPFILE recordlength=500000
Export: Release 10.2.0.4.0 - Production on Mon Apr 19 16:13:37 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Note: RECORDLENGTH=500000 truncated to 65535
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
。。。。。。。
. exporting statistics
Export terminated successfully with warnings.
real 1m41.524s
user 0m0.662s
sys 0m6.432s
可以看到直接路径明显要快于在相同buffer大小的常规路径导出操作。