Creating Temporary Join Tables [Performance]
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 ON 1=1 AND ANY_TABLE.ID = ANOTHER_TABLE.ID ;
Example - Step 2: Replacing Repeated Join Statements With Temporary Join Table
SELECT ... FROM temp2 WHERE 1=1 AND any_column IN ('example') AND any_column <> 0 ; SELECT ... FROM temp2 JOIN SECOND_TABLE ON 1=1 AND temp2.ID = SECOND_TABLE.ID AND SECOND_TABLE.any_column IN ('example1','example2') AND temp2.any_column <> 0 ;