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 ;