OraFAQ Forum: Server Utilities » Time taken in export and import

Home » RDBMS Server » Server Utilities » Time taken in export and import (Oracle 9.2.0.6.0,Sunos)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time taken in export and import [message #543070]

Mon, 13 February 2012 01:27

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi,

When i do a table export , it got over in 30 mins.When i do import using same dump file
(that was created in 30 mins), its taking more than 30 mins .

Can you guys tell the reason as why the import is taking more time than the export time ?

Thank you

Report message to a moderator

Re: Time taken in export and import [message #543071 is a reply to message #543070]

Mon, 13 February 2012 01:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Because write is slower than read and because "indexes data" are not exported but indexes are recreated and so on.

Regards
Michel

Report message to a moderator

Re: Time taken in export and import [message #543097 is a reply to message #543071]

Mon, 13 February 2012 02:20

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thank you for your explanations.

Quote:

because "indexes data" are not exported but indexes are recreated and so on.

When indexes data are not exported then how do you say the indexes are recreated and so on.

you mean indexes are recreated during import ?

Can you please clarify my doubts?

Regards

Report message to a moderator

Re: Time taken in export and import [message #543104 is a reply to message #543097]

Mon, 13 February 2012 02:33

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Only index metadata (its definition) is exported and so index are (re)created when you import (by executing a CREATE INDEX that is in the dump file).

Regards
Michel

Report message to a moderator

Re: Time taken in export and import [message #543127 is a reply to message #543104]

Mon, 13 February 2012 03:16

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

By reading your answers, i would say it is good practice to export objects with indexes=n and do 2 steps of import with

1.indexes=n and constraints=n , rows=y
2.indexes=y and constraints=y and rows=no (which is the default ) which will faster data insertion and saves time.

Please clarify my points and you can add some more points if you want (just want to know more about it).

Thank you

Report message to a moderator

Re: Time taken in export and import [message #543135 is a reply to message #543127]

Mon, 13 February 2012 03:28

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Laughing

Report message to a moderator

Re: Time taken in export and import [message #543156 is a reply to message #543127]

Mon, 13 February 2012 04:13

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No one is faster, you have to do the same work in the end.

Regards
Michel

Report message to a moderator

Re: Time taken in export and import [message #543169 is a reply to message #543156]

Mon, 13 February 2012 04:45

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Sorry, i reviewed my answer and found one mistake in export.Please find the below corrections.

By reading your answers, i would say it is good practice to export objects as it is, as the indexes and
constraints are y by default and do 2 steps for import with

1.indexes=n and constraints=n , rows=y
2.indexes=y and constraints=y and rows=no which will faster data insertion and saves time.

Thank you

[Updated on: Mon, 13 February 2012 04:47]

Report message to a moderator

Re: Time taken in export and import [message #543171 is a reply to message #543169]

Mon, 13 February 2012 04:51

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Ram ,

If you find mistakes in the answers, do not laugh rather correct the mistakes if you know atleast.

Report message to a moderator

Re: Time taken in export and import [message #543184 is a reply to message #543171]

Mon, 13 February 2012 05:37

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

It's not really possible to say if it's "good practice" or not in general.

It *might* be good practice, if you want to make the downtime shorter, and you are able to put the application online again without some of the indexes.

For example, I do upgrades on an application where there are a few tables with big indexes which are only used during day-end processing. So I can put the application online again for the users, and re-build that specific indexes while the users are already working again. But just blindly changing the export/import the way you suggest is not really a good idea. The overall process will definitely not be faster.

Report message to a moderator

Re: Time taken in export and import [message #543188 is a reply to message #543184]

Mon, 13 February 2012 06:00

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Ok Thomas.

If that is the case , then you suggest me the method to follow for faster export/import.

Report message to a moderator

Re: Time taken in export and import [message #543194 is a reply to message #543188]

Mon, 13 February 2012 06:34

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Everyone is trying to tell you that neither method is faster.

Report message to a moderator

Re: Time taken in export and import [message #543195 is a reply to message #543194]

Mon, 13 February 2012 06:36

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

http://www.orafaq.com/wiki/Import_Export_FAQ#How_can_one_improve_Import.2F_Export_performance.3F

the fastest way to export/import is to NOT DO IT AT ALL

http://www.dba-oracle.com/oracle_tips_load_speed.htm

[Updated on: Mon, 13 February 2012 06:40]

Report message to a moderator

Re: Time taken in export and import [message #543196 is a reply to message #543195]

Mon, 13 February 2012 06:40

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

And if that doesn't work:

Buy faster hardware.

If there would be a "guaranteed" and "simple" technique to make export/import faster, then that technique would have been already implemented in the standard export/import

Report message to a moderator

Re: Time taken in export and import [message #543197 is a reply to message #543196]

Mon, 13 February 2012 06:41

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Or atleast upgrade to latest supported versions and
use Datapump which is faster than traditional.

Report message to a moderator

Previous Topic:	which client do i need to use Data Pump Tools?
Next Topic:	exclude clause related

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:40:11 CST 2025