Home » RDBMS Server » Server Utilities » Time taken in export and import (Oracle 9.2.0.6.0,Sunos)
Time taken in export and import [message #543070] Mon, 13 February 2012 01:27 Go to next message
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
Re: Time taken in export and import [message #543071 is a reply to message #543070] Mon, 13 February 2012 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Time taken in export and import [message #543097 is a reply to message #543071] Mon, 13 February 2012 02:20 Go to previous messageGo to next message
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
Re: Time taken in export and import [message #543104 is a reply to message #543097] Mon, 13 February 2012 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Time taken in export and import [message #543127 is a reply to message #543104] Mon, 13 February 2012 03:16 Go to previous messageGo to next message
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
Re: Time taken in export and import [message #543135 is a reply to message #543127] Mon, 13 February 2012 03:28 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Laughing
Re: Time taken in export and import [message #543156 is a reply to message #543127] Mon, 13 February 2012 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one is faster, you have to do the same work in the end.

Regards
Michel
Re: Time taken in export and import [message #543169 is a reply to message #543156] Mon, 13 February 2012 04:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Time taken in export and import [message #543184 is a reply to message #543171] Mon, 13 February 2012 05:37 Go to previous messageGo to next message
ThomasG
Messages: 3211
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.
Re: Time taken in export and import [message #543188 is a reply to message #543184] Mon, 13 February 2012 06:00 Go to previous messageGo to next message
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.
Re: Time taken in export and import [message #543194 is a reply to message #543188] Mon, 13 February 2012 06:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Everyone is trying to tell you that neither method is faster.
Re: Time taken in export and import [message #543195 is a reply to message #543194] Mon, 13 February 2012 06:36 Go to previous messageGo to next message
ramoradba
Messages: 2456
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 Go to previous messageGo to next message
ThomasG
Messages: 3211
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
Re: Time taken in export and import [message #543197 is a reply to message #543196] Mon, 13 February 2012 06:41 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Or atleast upgrade to latest supported versions and
use Datapump which is faster than traditional.
Previous Topic: which client do i need to use Data Pump Tools?
Next Topic: exclude clause related
Goto Forum:
  


Current Time: Thu Mar 28 14:11:36 CDT 2024