Optimize nested joins.

babs4JESUS

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

 

 

kaurora

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. 

babs4JESUS

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 ?

 

kaurora

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.