Re: Q: emulating SQLPLUS COPY

Thomas Kyte (tkyte_at_oracle.com) wrote:
: 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;

I've tried that. The two problems with that are either the rollback segments run out of space or the remote system times out because it cannot maintain a consistent view of its copy of the table (which I guess is basically the same problem - lack of space).

That is why I'm trying COPY - it allows a commit to occur frequently so we don't have that problem. (I'm not entirely clear why committing at this end should solve the "consistent view" problem at the remote end, but it does.)

The problem with COPY is that it replaces the entire table, which means that in the next replication step (which is done by Oracle replication via snapshots), the entire table is copied again.

Other tables in the system are copied via a procedure that updates changed rows and inserts new rows. (Deletes are ignored) This way the oracle replication which runs next can automatically minimize the data copied to the next set of hosts.

The COPY command is really to provide a base case so that I know what should be possible in terms of time to copy.

(And no, there is no possibility of increasing the amount of space.)

: 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
: >

I forgot to mention that I am commiting every 100 rows. I've tried a few different numbers for that, every 10 rows, and also every 10,000 rows. That didn't seem to make much difference. Received on Sun Apr 07 2002 - 21:11:31 CDT