TIP #40 : Different options for creating table from select

I faced with a request from a client who asked me to create table from some base tables in warehouse database.Base tables are big tables with more 1000 milion records.
My goal was to create table as quickest as possible.
Here are different optins for create table as select statement

1. Simple create as select (CTS) with no option
2. CTS with nologging option
3. CTS with unrecoverable option
4. CTS with parallel option
5. CTS with parallel and nologging option

Nologging or unrecoverable option create redo log as minimum as possible. (Database should be in no force logging mode).

The following shows practical example of above option.

========== Option 1 : Simple CTS =======================

SQL> create table tbl1 as select * from dba_segments;
Table created.
Elapsed: 00:00:43.04

SQL> select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 405476 <== 405K redo was generated

========== Option 2 :Nologging option =======================

SQL> create table tbl2 nologging as select * from dba_segments;
Table created.
Elapsed: 00:00:42.06 <== Not major improvement in SQL execution

SQL> select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 436128 <== Only 30K redo logs were generated. (Major improvement in redo generation)

========== Option 3 :Unrecoverable option =======================

SQL> create table tbl3 unrecoverable as select * from dba_segments;
Table created.
Elapsed: 00:00:45.00 <== Not major improvement in SQL execution

SQL> select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 466688 <== Only 30K redo logs were generated. (Major improvement in redo generation)

========== Option 4 :Parallel option =======================

SQL> create table tbl4 parallel (degree 4) as select * from dba_segments;
Table created.
Elapsed: 00:00:14.09 <== Major improvement in SQL execution

SQL> select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 894304 <== 400K redo generation as normal

SQL> create table tbl5 parallel as select * from dba_segments;
Table created.
Elapsed: 00:00:16.03 <== Major improvement in SQL execution

SQL> select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 1314768 <== 400K redo generation as normal

========== Option 5 :Parallel and nologging option =======================

SQL> create table tbl6 parallel unrecoverable as select * from dba_segments;
Table created.
Elapsed: 00:00:26.00 <== Still has improvement in total execution

SQL> select sid,name,value from 2 v$statname a,v$mystat b 3 where a.STATISTIC#=b.STATISTIC# 4 and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 1366836 <== (52K) - Redo log less than400K (Normal redo size) was generated

========== Other option =======================

Direct Insert:

SQL> create table tbl7 unrecoverable as select /*+ append */ * from dba_segments;
Table created.
Elapsed: 00:00:41.08 <== Not major improvement

SQL> select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 1416912 <== (50K) - Redo logs less than normal was generated

SQL> create global temporary table tbl8 as select /*+ append */ * from dba_segments;
Table created.
Elapsed: 00:00:42.01 <== No improvement in total execution

SQL> select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';

SID NAME VALUE

---------- -------------------- ----------

20 redo size 1440580 <== (23K) - Minimum redo logs were generated

=====

Combination of parallelism and nologging isthe best choice with respect to performance.Make sure database is not in "force logging" mode.Tuning select statement before running CTS command is highly recommended.