Faster CTAS Statements

[Deleted User]

Jim Knicely authored this tip.

In a CREATE TABLE statement, you can specify an AS clause to create a table from a query (a.k.a. CTAS statement).

When dealing with a large SELECT statement result set, your CTAS should perform much better if you specify the DIRECT load method!

Example:

dbadmin=> SELECT TO_CHAR(COUNT(*), '999,999,999,999') row_count FROM big_table;
    row_count
------------------
      226,492,416
(1 row)

dbadmin=> SELECT TO_CHAR(COUNT(*), '999,999,999,999') row_count FROM smaller_table;
    row_count
------------------
       56,624,482
(1 row)

dbadmin=> \timing on
Timing is on.

dbadmin=> CREATE TABLE combined_table AS
dbadmin->   SELECT big_table.pk, big_table.some_values, smaller_table.some_values2
dbadmin->     FROM big_table
dbadmin->     JOIN smaller_table
dbadmin->    USING (pk);
CREATE TABLE
Time: First fetch (0 rows): 54789.067 ms. All rows formatted: 54789.168 ms

dbadmin=> CREATE TABLE combined_table2 AS /*+ DIRECT */
dbadmin->   SELECT big_table.pk, big_table.some_values, smaller_table.some_values2
dbadmin->     FROM big_table
dbadmin->     JOIN smaller_table
dbadmin->    USING (pk);
CREATE TABLE
Time: First fetch (0 rows): 23812.770 ms. All rows formatted: 23812.782 ms

Have fun!