Re: Q: emulating SQLPLUS COPY

In article <3cae4695_at_news.victoria.tc.ca>, yf110_at_vtn1.victoria.tc.ca says...
>
>Hello...
>
>I can use SQL PLus COPY command to copy a large table (with no constraints
>or indexes) and it takes about 35 minutes.
>

hows about:

insert /*+ append */ into local_table
select * from remote_table;

never do procedurally what you can simply do in a single statement.

>I want to do the same thing via PL/SQL, but it takes much longer (ten
>hours).
>
>My PL/SQL code has the following very simple outline. Initially the local
>table is empty, and has no constraints or indexes.
>
>
> cursor C is select * from remote_table
>
> one_row local_table%rowtype; -- both tables have same layout
>
> open C
> loop
> fetch C into one_row
> exit when end of data
>
> insert into local_table values
> ( one_row.col1 ,
> one_row.col2 ,
> ... ,
> one_row.col67
> );
>
> end loop
> close C
>
>As you can see it's very simple. It just loops over the remote table
>rows, and for each row it does an insert into the local table.
>
>However, as I said, this is *much* slower than the COPY command. What is
>the trick to speed this up?
>
>I need to use a procedure (not the COPY command) because eventually I need
>to do some additional processing of each row.
>
>Suggestions welcome, thanks.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp