MVSFORUMS.com

[abhayasahoo]

Does order of predicates in an SQL query with a JOIN matter as far as the performance of the query is concerned ?

For example,

SQL1
Select A.col1, A.col2, B.col3

from TBL1 A
,TBL1 B

where A.col6 = B.col6 >>>> This is the join condition
and A.col4 = '7777779' and
A.col5 = 0 and
B.col4 = 'ABC';

SQL2
Select A.col1, A.col2, B.col3
from TBL1 A
,TBL1 B

where A.col4 = '7777779' and
A.col5 = 0 and
B.col4 = 'ABC' and
A.col6 = B.col6 >>>> This is the join condition


Which of the SQLs will be faster with DB2 V7 (and later versions) or both will have the same performance ?

[kolusu]

abhayasahoo,

We cannot just tell you just by looking at the sql's. we need to know how the indexes are defined on the columns. Run an explain on the both the sqls' and then you will have fair idea about the predicate evaluation.

I would suggest that you read this which explains in detail about General rules about predicate evaluation

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/6.3.3?DT=20010710165542

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu