Oracle Create Table as Select

CTAS employs the direct path load, in other words, it skips loading data into buffer cache.

 

PGA consumption

Consider two scenarios:

a) Create table target as select * from source;

The result of select statement is not saved in memory, Oracle writes it directly to disk. Even if source table is large, PGA and Temp is not used.

 

b) Create table target as select col1, sum(col2) from source group by col1;

Oracle has to do Hash Group By for this kind of query; PGA and temp possibly are used.

 

To create a new partitioned table, user needs to issue:

Create table xxx

Partition by range (colx)

(

Partition pxxx values less than ()

)

As Select * from xxx

 

To create the table in parallel, you can either:

1). Create table xxx parallel y as select * from zzz;
2). Create table yyy as select /*+ parallel */ * from zzz;

There's a major difference between statement 1 and 2. For statement 1, Oracle parallelizes both create and select parts, while for 2, only the select part is parallelized. When creating table in parallel,  each parallel execution server allocates a new extent, and fills it with data. 

Oracle documents state:
The CREATE operation of CREATE TABLE ... AS SELECT can be parallelized only by a PARALLEL clause or an ALTER SESSION FORCE PARALLEL DDL statement.
When the CREATE operation of CREATE TABLE ... AS SELECT is parallelized, Oracle Database also parallelizes the scan operation if possible. 

The scan operation cannot be parallelized if, for example:

• The SELECT clause has a NO_PARALLEL hint.

• The operation scans an index of a nonpartitioned table.

When the CREATE operation is not parallelized, the SELECT can be parallelized if it has a PARALLEL hint or if the selected table (or partitioned index) has a parallel declaration.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1061114/，如需轉載，請註明出處，否則將追究法律責任。