CTAS

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).

History

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

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;

Also see

• Nologging and force logging - speed-up CTAS operations

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	#