Oracle Create Table as Select

最新推荐文章于 2025-04-17 13:05:16 发布

原创最新推荐文章于 2025-04-17 13:05:16 发布 · 5.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#CTAS #Parallel

Oracle 专栏收录该内容

33 篇文章

订阅专栏

本文探讨了CTAS（Create Table As Select）操作在Oracle数据库中的实现方式，特别是在使用直接路径加载时如何绕过缓冲区缓存。文章对比了不同场景下CTAS操作对PGA内存消耗的影响，并详细介绍了分区表创建及并行CTAS操作的特点。

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

Parallelized CTAS

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.