Insert Append vs CTAS “Create Table as Select” to Copy Data

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

Advertisements