Datapump Import ( IMPDP ) Is Slower When Table Exists With TABLE_EXISTS_ACTION=TRUNCATE

Why Datapump Import ( IMPDP ) Is Slower When Table Exists With TABLE_EXISTS_ACTION=TRUNCATE?

The short reason is that oracle didn’t use direct load when using TRUNCATE option. With replacing or empty schema, impdp can use direct load which is the fastest way. There is an oracle document talked about it.

Datapump Import ( IMPDP ) Is Slower When Table Exists With TABLE_EXISTS_ACTION=TRUNCATE [ID 780784.1]


Cause
This is due to the datapump import using two different load methods.
When the table doesn’t pre-exist direct path load can be used, but when the table does pre-exist (and is an IOT (Index Organized table) and hence Primary Key constraint / unique index are applicable, we can only use external table load method .
This is discussed in Bug 8205365 which is closed as not a bug due to above reason.
Solution

This is not a bug and same thing is also documented in utilities guide as below.
http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_overview.htm#CJAJJJBH

Oracle online document also addressed this issue.
http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_overview.htm#CJAFDGIC

Using Direct Path to Move Data

After data file copying, direct path is the fastest method of moving data. In this method, the SQL layer of the database is bypassed and rows are moved to and from the dump file with only minimal interpretation. Data Pump automatically uses the direct path method for loading and unloading data when the structure of a table allows it. For example, if a table contains a column of type BFILE, then direct path cannot be used to load that table and external tables is used instead.

The following sections describe situations in which direct path cannot be used for loading and unloading:

Situations in Which Direct Path Load Is Not Used

If any of the following conditions exist for a table, then Data Pump uses external tables rather than direct path to load the data for that table:

  • A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned.
  • A domain index exists for a LOB column.
  • A table is in a cluster.
  • There is an active trigger on a preexisting table.
  • Fine-grained access control is enabled in insert mode on a preexisting table.
  • A table contains BFILE columns or columns of opaque types.
  • A referential integrity constraint is present on a preexisting table.
  • A table contains VARRAY columns with an embedded opaque type.
  • The table has encrypted columns.
  • The table into which data is being imported is a preexisting tableand at least one of the following conditions exists:
    • There is an active trigger
    • The table is partitioned
    • Fine-grained access control is in insert mode
    • A referential integrity constraint exists
    • A unique index exists
  • Supplemental logging is enabled and the table has at least one LOB column.
  • The Data Pump command for the specified table used the QUERY, SAMPLE, or REMAP_DATA parameter.
  • A table contains a column (including a VARRAY column) with a TIMESTAMP WITH TIME ZONE datatype and the version of the time zone data file is different between the export and import systems.
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: