We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Optimize nested joins. — Vertica Forum

Optimize nested joins.

Hi,

 

If we have a query like the following :

 

select * from a left join b on a.id1= b.id2,

 

We can easily optimize by making a projection of a sorted by id1 and a projection of b sorted by id2(thus forcing a more efficient merge join).

 

But if we have a query like the following :

 

select * from (select * from a left join b on a.id1= b.id2) as c left join d on c.id1 = d.id4.

 

How would we proceed ? How do we ensure merge joins in this case ? We can make a projection of d sorted by id4, projection of a sorted on id1 and projection of b sorted by id2, but this does not ensure a merge join.

 

Thanks

 

 

Comments

  • Have you tried order by c.id1 assuming d is already sorted on id4? Might not be a solution but I would check the explain plan for it atleast. 

    This depends on the amount of data your nested query is returning. 

  • Hi Kaurora,

     

    Thanks for your reply.

     

    I can easily get d sorted on id4, but I am not sure how I can c sorted on id3 as c itself is the result of a join. I am not sure how I can get a projection to do that. Could you please advise here ?

     

  • select * from (select * from a left join b on a.id1= b.id2 order by id1) as c left join d on c.id1 = d.id4.

Leave a Comment

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