Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Avoid Left Joins

We load .tar files (has 1000's gz files) to vertica staging tables. From staging table, transformations are applied (by left joining several tables) and then loading to Vertica tables.

Sample Query,
Insert into Vertica_Table (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)
select
STG.col1, TAB2.col2, TAB3.col3,..........................., TAB10.col10
From
Staging_Table STG Left Outer Join
Table_1 TAB1 on (join conditions) Left Outer Join
Table_2 TAB2 on (Join conditions) Left Outer Join
.
.
.
Table_3 TAB3 on (join conditions)

We are using left joins to do transformations / mapping. This query runs for ever. STG (is a huge table 1TB data).

Please let me know if there is any alternate approach to improve the performance. Or can this transformation be done by looping every record in STG (using seperate procedure / UDL etc).

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.