Re: What's quicker, table to table insert or extract and sqlload?

Andy Hardy (aph_at_ahardy.demon.co.uk) wrote:
: Hi,
 

: I've a 6 million row table that needs to be copied to another, to
: produce identical copies.
 

: I'm contemplating either performing a very large insert (will I have
: the rollback to support it!) or extracting the first table to a file
: for subsequent loading by sqlload.
 

: Any thoughts or suggestions?

My strong suspicion (i have no datapoints to back this claim up) is that your fastest method is to "create table ... as select * from ... unrecoverable...". The unrecoverable piece is only available in 7.2+.

a close second (though a bit complicated) would be extracting the first table to a pipe (in UNIX) and have loader read that pipe as the input file (using direct loader option, of course).

Johnny Chan
Independent Oracle Specialist Received on Thu Feb 01 1996 - 00:00:00 CET