Hi All,
Does the order in which the tables are placed inside a join matter for getting better performance?
Assuming, below query what is the best way to re-arrange the join order? what considerations to be taken care?
select
t1.c1,t1.c2,t1.c3,t1.c6,
t2.c1,t2.c3,t2.c4,
t3.c1,t3.c2,t3.c3,
t4.c1,t4.c2,t4.c3
from t1 inner join t2 on t1.c2 = t2.c4
inner join t3 on t3.c1 = t2.c3 and t3.c3 = t1.c6
inner join t4 on t4.c2 = t3.c1
where t1.iscurrent = 1
and t3.dt between 'date-1' and 'date-2'
sample rowcounts
================
t1 = 150 rows
t2 = 9634765 rows
t3 = 9000 rows
t4 = 70 rows
Thanks,
Sam
Join order won't define execution order most of the times. My suggestion is that you write your JOINs in a logical order that would make easier for developers to read and modify.
I would clarify that this is true only when all of the joins are INNER or CROSS joins. Once you add OUTER joins, the order definitely does make a difference.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA