Thanks for the question, Ron.
Asked: April 24, 2012 - 8:02 pm UTC
Last updated: April 25, 2012 - 1:28 pm UTC
Version: 11.2.0
Viewed 1000+ times
You Asked
Just a quick opinion check...
I'm cloning a table from another database using CREATE TABLE AS SELECT across a database link from inside a PL/SQL package using dynamic SQL.
A colleague said I should check out the SQL*Plus COPY command as it might be more efficient.
From what I've seen online, the COPY command still exists in 11g even though it's not listed in the documentation, although it's apparently been deprecated.
I haven't been able to find much comparing the two, so I thought I'd ask you for your opinion. I'm leaning towards not using COPY purely for the fact that it's being deprecated, which tells me that Oracle isn't very interested in it.
I've got databases here I could test the theory against, but when I saw that it wasn't included in the documentation, I became concerned.
Thanks for any info...
Ron
and Tom said...
The copy command would probably NOT be the way to go.
With the copy command - you'll be connected to database1 and it will then open another connection to database2.
It will then SELECT from database1 and retrieve the data to the sqlplus client and then array INSERT that data from the sqlplus client to database2. Thus, the data tends to travel a lot further.
with a database link - you just pull the data from database1 into database2 - no pull to a client, push to a server.
The copy command has limited datatype support - it doesn't support much of any of the new types added since about 8.0.
I would not really recommend it as the first approach, the only advantage it might have would be that it can copy a LONG type if you know the maximum length of your longest long.
Others might say it also has an advantage in that it can commit ever N records. I firstly disagree that that would be an advantage (create table as select can skip undo and redo - there is no real need to commit every now and again). And if you really thought it was, it is nothing that dbms_parallel_execution in 11.2 could not give you as far as breaking an insert up and committing the bits just as easily (if not more so)
Is this answer out of date? If it is, please let us know via a Comment