CTAS : Create Table as

We all are very familiar with CTAS or 'Create Table as' clause that used to construct new table and populated it with data extract by 'Select' statement. That means table creation and population are done by executing a single sql CTAS statement.

Fom example,

CREATE TABLE employee_info As
SELECT emp.id, emp.name, dp.name FROM employee emp, department dp WHERE emp.dp_id =dp.id;

The above CTAS statment create a new table named 'employee_info' and store the result of SELECT statement into that table.

Here are some common use of CTAS statement:

• Store the result of select statement into a table.
• create a duplicate table for backup purpose.
• Reorganize existing table for better performance.

There are some limitations of CTAS statement:

• Can't create indexes ( in case of table duplication or Reorganization).
• Can't create primary, unique or foreign key constraints ( in case of table duplication or Reorganization). But it can create 'NOT NULL' constraint.
  

• Though CTAS mirrors metadata structure, it can't mirror 'DEFAULT' value checker for columns.

I have learned the 3rd pitfall of CTAS recently. I used CTAS to construct a duplicate table ( a sub task of table partitioning). The partitioning job was successful but the problem arose when we inserted new rows into the partitioned table. One of the column got null value stored instead of zero (the column's default value was zero). Through an investigation we discover that CATS statement skipped default value check of that column.