Q: emulating SQLPLUS COPY

Hello...

I can use SQL PLus COPY command to copy a large table (with no constraints or indexes) and it takes about 35 minutes.

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. Received on Fri Apr 05 2002 - 18:51:33 CST