Export/Import of Large Tables

[rsv1000sp]

We would like to export a large table from one environment to another, but as the table is 7.5 million rows in size our current estimate is that it will take approx. 34 hrs !!!

Is this correct ?

Or are we doing something wrong ?

 

[Turkbear]

Hi,
More info please: OS, Version of Oracle, how you derived the estimate....

Thanks,

 

[rsv1000sp]

AIX 4.3.3 running on a IBM RS6000 SP - 8-way
Oracle 8.1.7

Using Oracle commands.

Estimate of timing was taken from total number of records that were processed during an attempt to do the export about a month or so ago, which failed after 12hrs.

 

[karluk]

Did your previous attempt use direct path export? If not, you should give it a try. It's supposed to be much faster than conventional exports. The parameter to use is "DIRECT=Y".

 

[rsv1000sp]

The failure does not matter that much, it's the fact that we believe it will take so long.

I was jsut wondering what other users have experienced with tables of this size ?

 

[Turkbear]

Hi,
Just as a test because I was curious,I exported 600,000 rows from 2 tables( a 65MB DMP file was produced) in 4.6 minutes..
I was using a W2K server with 1 gig memory, Pentium 3 processor, Oracle 8.1.5 ( I know but that's where the data was) so your time seems very long, unless there are lots of fields per row..
If I extrapolate I could get @8 Milion rows in
an Hour or so....

 

[rcurva]

Use UNRECOVERABLE clause also so that the loading will not be logged in the the redo.

example;

sqlload UNRECOVERABLE userid=.... ...


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[DoubleH]

Hi,
If You think the time taken is too long, then you could go very well for direct path loading as karluk had noted. That will give a significant time improvement. Thanks & Regards,
DoubleH

 

[rsv1000sp]

Thanks for the pointers so far gents. I think I should have given more info on the table size, here's the latest on the table size:

Number of columns = 104
Number of rows = 9864871
Size in Gb = 6.8
Index size in Gb = 18.4Gb - combined 34 indexes!!!

 

[rcurva]

Can you do the following sequence instead?

1. DROP the indexes
2. LOAD the data
sqlload UNRECOVERABLE ... DIRECT_PATH=Y
3. RECREATE the indexes

I suppose this is a one-time job only, right?
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rsv1000sp]

It's possible that we may need to do this at least a couple of times a year to export the table from a live to development environment for testing purposes.

I am trying to ascertain if someone could confirm whether or not the time we have esitmated is correct or wildly inaccurate indicating we have a problem with our environment or set-up ?

 

[rcurva]

Your estimate could be accurate, considering the 34 indexes that you have to update for each record that you will be loading.



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rsv1000sp]

Robbie

That's not the answer I wanted to hear, but thanks for that.

Is that from personal experience of large table usage yourself ? If so what platform ?

 

[Turkbear]

Hi again,
With the 34 Indexes and a table of that size, I'm afraid the time estimate is probably pretty close...
While I don't import anything that large, I do load a 4M row table every night and if I do not drop the indexes
first ( there are 5 of them) the load time increases by a factor of at least 10 ....

 

[rcurva]

I haven't tried loading that big of a table, but I tried migrating from one Oracle environment to another.

If your source is another Oracle DB. You could do an export/import instead. If not, then my prevous suggestion on sqlload will do.

Either way, I definitely recommend dropping the indexes first before loading/inserting the data.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War