Insert Append vs CTAS “Create Table as Select” to Copy Data
Posted: January 4, 2011 Filed under: Oracle 2 Comments »Some of the most basic tasks a DBA is asked to do, is to move data across schemas or tablespaces. This post compares 2 famous options: CTAS and Insert with append hint. Our comparison revealed that parallel CTAS is about 50% faster than Insert Append.
Test Details:
Table: 2 GB approx, no LOBs, source and target tables are not partitioned
Oracle version: 10.2.0.4 installed on HP Itanium with 4 CPUs
Instance: Single instance ( no RAC), database in noarchive mode
File System: UFS
Here are the test results:
|
Insert Append Test Cases |
||
| Test Case | SQL | Exec in Secs |
| no append | insert into dest select * from source1;
|
189 |
| append | insert /*+ append */ into dest select * from source1;
|
87 |
|
CTAS Test Cases |
||
| Test Case | SQL | Exec in Secs |
| CTAS, no parallel | create table dest as select * from source1;
|
93 |
| CTAS Parallel | alter session force parallel ddl parallel 3; alter session force parallel query parallel 3; create table dest as select * from source1;
|
44 |
Hazem Ameen
Senior Oracle DBA
