Question: What is the fastest way
to copy Oracle data from one table to another table?
Answer: You can use many tricks to
make a faster copy of Oracle data using CTAS:
- Use NOLOGGING: This turns off
logging, making for a faster copy. However, if you
must recover, you will not be able to roll-forward
through this operation.
- Use the parallel clause: Since
a table copy does a full table scan of both tables,
parallel query will make the copying far faster, (up to
cpu_count-1 faster).
In this example we fast copy data from one table to
another table on a 16 CPU server:
create table
newtab
parallel 15 nologging
as
select
/*+parallel(source 15) */ *
from
oldtab;
Also see these notes on fast copying of Oracle
tables/schemas:
