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

Oracle 1 Minute

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

Published by ksadba

My name is Hazem Ameen. I'm an Oracle DBA with 15+ years of experience. I've been living and working in Riyadh, Saudi Arabia since 2003. Prior to that, I lived and worked in Chicago Illinois. Through this blog, I plan on sharing some technical tips and life experience in Saudi Arabia. Those who moved to KSA or planning on doing so might find some useful info here.

Published

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

  1. Hi Hazem,
    Nice analysis, how about enabling parallel and do the insert with append? Will that be better than CTAS with parallel?

    Thanks

    Reply

    1. CTAS with parallel was always faster than insert with append (parallel or no parallel).

      If I have time, I will test with partitioned tables

      Reply

Leave a comment