Re: SQL*PLUS: Copy table or INSERT INTO table_at_remotedb?

Antonio Sant wrote:
>
> Hi there!
>
> Only a quick question:
>
> What is better?
>
> I have to copy a table (300.000 rows) from a local DB to a remote one, I'm
> wondering which is faster or better (ie. less cpu stress, less log
> activity...)
>
> The idea is:
>
> - Log in the remote db
> - truncate table
> - insert or (sqlplus)copy the table

I have found the copy command to be somewhat slow. I would either create a pl/sql procedure to perform the insert (allowing commits), or dropping the table (since you are truncating anyway), running a 'create table as select' statement, then rebuilding the indexes. I find this is usually the quickest way to drag data across a database link (assuming you have the tempspace to do it). It you use the 'nologging' clause, it will also avoid redo activity.

Cheers,
GC Received on Fri Dec 01 2000 - 01:51:53 CET