What is CTAS?

You have probably heard other DBAs/developers and websites talk about creating a database table through CTAS. Does that term leave you befuddled? There’s a very simple explanation to that acronym – read on!

Demystifying the acronym CTAS

CTAS stands for CREATE TABLE AS SELECT.

You will typically use the CREATE TABLE AS SELECT syntax when you want to base a new table’s structure on an existing table, and also populate the new table with data from the existing table. In the absence of the CTAS, you would have to create an empty table first and then populate it via something like a MERGE command. This sequence of steps is encapsulated and simplified in a single DDL command.

An example: You want to create a table with the same structure as SCOTT’s EMP table, but containing only employees who have been hired in the past year.

Using CTAS, the statement will be:

SQL> create table emp1
  2  as
  3  select * from emp
  4  where hiredate >
  5    (SYSDATE - 365);

Table created.

CREATE TABLE AS SELECT not only creates the table but also copies the content as per the select statement.

Note that the command does not copy other peripheral objects such as indexes, constraints, synonyms or partitions.

Applications of CTAS

The most common uses of CREATE TABLE AS SELECT are in these scenarios:

• Creating a table identical to another table in structure, but with a filter criteria applied to its data. The EMP_NEW table shown above is an example.
• Creating a table with small structural differences from an existing table. The pre-9i approach to renaming a table column used CTAS.