True or False – Data Dump Import and Indexes

6 05 2010

May 6, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

While reading one of the OTN threads that asked whether Data Pump import will automatically rebuild indexes, I notice that someone posted an interesting response that appeared to be quoted from another source.  I searched the Internet and found the text that was copied into the OTN post, directly below a heading that read “Maximizing Oracle Import (impdp) Performance” – it was then that I decided it was time for another true or false quiz.

The reading material:

  1. http://www.dba-oracle.com/oracle_tips_load_speed.htm
  2. http://download.oracle.com/docs/cd/E14072_01/server.112/e10701/dp_import.htm
  3. http://download.oracle.com/docs/cd/E14072_01/server.112/e10701/dp_export.htm

Read through the above material and attempt to answer the following questions – as in the past, state why the answer is true or false.  Since the first article was last revised in March 2006, I probably should have referenced the documentation for Oracle Database 10g R2, but the Oracle Database 11g R2 documentation should be sufficient.

1.  The options used when data is exported with Data Pump Export (expdp) have no control over how the data may be imported with Data Pump Import (impdp).

2.  Data Pump Export supports enabling and disabling direct path for exports.

3.  Set the ANALYZE parameter of impdp to N to prevent statistics from being gathered on imported tables and indexes.

4.  Using solid state disks (SSD) will speed up import rates when the import job is otherwise fully tuned.

5.  Setting the COMMIT parameter of impdp to N suppresses the committing of the imported data until the load process ends.

6.  Setting the INDEXES parameter of impdp to N prevents indexes from being created until the import process completes.

7.  Setting the INDEXES parameter of impdp to N eliminates the index maintenance overhead when data is imported into a pre-existing table having pre-existing indexes.

8.  When the creation of indexes is postponed through the use of the INDEXES parameter of impdp, the resulting text file should be edited to set the parallel degree of each index to one less than the value of the CPU_COUNT initialization parameter’s value.

9.  Using the EXCLUDE=STATISTICS parameter value of impdp prevents the automatic of gathering of object statistics by impdp.  Setting this parameter value will greatly improve impdp performance.

10. When the _disable_logging initialization parameter is set to TRUE, either a SHUTDOWN ABORT or instance crash will disable/corrupt the database.  This corruption may be prevented by creating a backup of the database before modifying the value of that hidden initialization parameter.

Edit – May 6, 2010: The original title of this blog article was intended to be “True or False – Data Pump Import and Indexes” – a bit of unintentional humor was caused by the typo in the article title.  There are several definitions of the word “Dump“, some of which are more fitting than others.

SchedulerAutomation.zip


Actions

Information

One response

7 05 2010
Charles Hooper

I am surprised that no one has yet commented that article #1 seems to have inter-mixed the options for exp (original export) with those of expdp (Data Pump Export), as well as inter-mixed the options for imp (original import) with those of impdp (Data Pump Import). I am sure that this is contributing to a bit of unnecessary confusion when trying to answer the questions.

11.2.0.1 Data Pump Legacy Mode (new with 11.2.0.1) – shows how to translate the parameters used with the original exp/imp utilities to the equivalent parameters used with the Data Pump Export/Import utilities.

Original Export
Original Import
Moving Data with Datapump, and the original export and import

Good luck with the questions.

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




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: