Rules for CREATE TABLE AS SELECT

The CREATE TABLE AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement. The CREATE part follows the same rules as other DDL operations.

This section contains the following topics:

Decision to Parallelize (Query Part)

The query part of a CREATE TABLE AS SELECT statement can be parallelized only if the following conditions are satisfied:

  • The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part of the statement has a PARALLEL clause specification or the schema objects referred to in the query have a PARALLEL declaration associated with them.

  • At least one table specified in the query requires either a full table scan or an index range scan spanning multiple partitions.

Degree of Parallelism (Query Part)

The DOP for the query part of a CREATE TABLE ... AS SELECT statement is determined by one of the following rules:

  • The query part uses the values specified in the PARALLEL clause of the CREATE part.

  • If the PARALLEL clause is not specified, the default DOP is the number of CPUs.

  • If the CREATE is serial, then the DOP is determined by the query.

Any values specified in a hint for parallelism are ignored.

Decision to Parallelize (CREATE Part)

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.

Degree of Parallelism (CREATE Part)

The DOP for the CREATE operation, and for the SELECT operation if it is parallelized, is specified by the PARALLEL clause of the CREATE statement, unless it is overridden by an ALTER SESSION FORCE PARALLEL DDL statement. If the PARALLEL clause does not specify the DOP, the default is the number of CPUs.