Skip to main content

Creating temporary join tables

If you use similar joins multiple times, create temporary tables that cover (sub-) joins and use them instead of executing the same join statements repeatedly.

Important

A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.

Example - Step 1: Creating temporary join table

DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2 AS
SELECT
   ...
FROM
   ANY_TABLE
   JOIN ANOTHER_TABLE
       ANY_TABLE.ID = ANOTHER_TABLE.ID
;

Example - Step 2: Replacing repeated join statements with temporary join table

SELECT
    ...
FROM temp2
AND any_column IN ('example')
AND any_column <> 0
;
 
SELECT
    ...
FROM temp2
JOIN SECOND_TABLE
   AND temp2.ID = SECOND_TABLE.ID
   AND SECOND_TABLE.any_column IN ('example1','example2')
   AND temp2.any_column <> 0
;