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

Yuk Hon Johnny Chan wrote:
>
> 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

or

1. use the sqlplus copy command. You can set the parameter COPYCOMMIT to commit after x number of inserts therefore large rollback segments are not required. See the SQL*Plus Users Guide. This will also handle tables with LONG fields (see SET LONG param.). Only limitation is it requires SQL*Net.
2. Imp/exp will allow you to import the table to a different user account with the same table name. This effectively produces a copy of the table. Should be relatively efficient.

NOTE : According to _Oracle : The Complete Reference_ (Koch&Looney), the

       'create table ... as select * from ...unrecoverable' option
       suggested by Mr. Chan will be the fastest since no redo log
entries
       are generated. It makes no mention of rollback segments. I
suspect
       that since this is a DDL and the operation cannot be rolled back, 
       there is no use of rollback segments - any feedback on this ??

phil

-- 
Philip Fielder     YellNet 505.665.3227     Fac. for Info.,Management,
EES-5/MS D452      FaxNet  505.667.1628     Analysis and Display
FIMAD/Los Alamos Nat. Lab	 	           
Los Alamos, NM 87545                   URL : http://ees.lanl.gov/fimad