TRUST_EXISTING_TABLE_PARTITIONS is a new option for the parameter data_option in the impdp utility of Oracle 12.2.

Previously, If we are loading data to an existing partitioned table using impdp, then despite mentioning parallel option, partitions were getting loaded one by one, Which slowdown the import process.

 

As per Oracle document:

TRUST_EXISTING_TABLE_PARTITIONS — tells Data Pump to load partition data in parallel into existing tables. You should use this option when you are using Data Pump to create the table from the definition in the export database before the table data import is started. This is done as part of a migration when the metadata is static and can be moved before the databases are taken off line in order to migrate the data. Moving the metadata separately minimizes downtime. If you use this option and if other attributes of the database are the same (for example, character set), then the data from the export database goes to the same partitions in the import database.

 

With this new option, partitions will be loaded parallelly, when importing to existing partitions.

Lets test both the scenarios (with and without TRUST_EXISTING_TABLE_PARTITIONS option)

1. Without TRUST_EXISTING_TABLE_PARTITIONS option:(DEFAULT OPTION)

Here we are appending data to an existing partitioned table:

While the job is running, press control + c, and check the job status:

What we see is, only one worker process is in EXECUTING state and rest are in WORK WAITING. i.e it is not using PARALLEL for import data.

This import took around 1 min 20 seconds to complete.

With TRUST_EXISTING_TABLE_PARTITIONS 

Check the import job status

Now if we see, all the 8 worker process are in EXECUTING STATE.i.e partitions are getting loaded in PARALLEL.

And import took only 19 seconds. 🙂

This option really saves a lot of time, if the requirement is to APPEND data to an existing partition table.