A direct path export should not be considered the converse of a direct path load using SQLLDR (for those of you familiar with that option). A direct path export does not read directly from the datafiles and write to the DMP file. SQLLDR does write directly to the datafiles from the DAT files. Export in direct path mode simply bypasses the SQL evaluation buffer (where clause processing, column formatting and such).
90% of the path is the same, EXP is still reading buffers into the buffer cache, doing the same consistent read processing and so on.
The speedup of the direct path export can be large however.
The 10% of the processing that it cuts out, accounts for a much larger percentage of the run-time.
For example, I have just exported about 100meg of data, and 1.2 million records. The direct path export took about one minute. The conventional path export on the other hand took three minutes. There is unfortunately no corresponding 'direct path import'. Import uses conventional SQL to insert the data back into tables. SQLLDR is still the way to go for high performance data loads.
It should be noted that in direct path mode, you cannot use the QUERY= parameter to select a subset of rows. This makes sense given that DIRECT=Y is simply a way to bypass the SQL evaluation buffer and that is where the where clause processing would normally take place.
followup to comment two below
Yes, you can move the dmp file -- the file format is the same regardless of the direct= setting.
No comments:
Post a Comment