CTAS

From Oracle FAQ
Jump to: navigation, search

CTAS refers to a CREATE TABLE AS statement - a new table is created and populated with row from a specified query. By default, only NOT NULL constraints will be copied to the target table (unless specified - see example below).

[edit] History

From Oracle 7 CTAS statements can be performed in parallel (Oracle Enterprise Edition only).

[edit] Examples

Simple CTAS statement:

SQL> CREATE TABLE emp2 AS SELECT * FROM emp;
Table created.

Specifying a tablespace:

SQL> CREATE TABLE emp3 TABLESPACE users AS SELECT * FROM emp;
Table created.

Parallel CTAS with nologging for faster performance:

SQL> CREATE TABLE emp4 NOLOGGING PARALLEL 4 AS SELECT * FROM emp;
Table created.

Normal CTAS, but also define a primary key on the target table:

SQL> CREATE TABLE emp5 (empid PRIMARY KEY) AS SELECT empid FROM emp;

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #