Faster CTAS Statements: Quick Tip

Posted June 20, 2018 by Sarah Lemaire, Manager, Vertica Documentation

white cloud in vault type room representing cloud computing
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!