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

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

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s