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
In this example we fast copy data from one table to
another table on a 16 CPU server:
parallel 15 nologging
/*+parallel(source 15) */ *
Also see these notes on fast copying of Oracle