i am running query like below

select field1 from Tab1 where (tab1.field2,tab1.field3,tab1.field4) in
(select tab2.field2,tab2.field3,tab2.field4 from Tab2 where
(tab2.field5,tab2.field6) in (select tab3.field5,tab3.field6 from tab3 where tab3.field7 = 'somevalue')
)

it takes long time to run. i have proper indexes.
Even when subquery
select tab3.field5,tab4.field6 from tab4 where tab4.field7 = 'somevalue'
return 0 records , it is slow

i changed query to use exists

select field1 from Tab1 where exists (select 1 from tab2
where (tab1.field2,tab1.field3,tab1.field4) = (tab2.field2,tab2.field3,tab2.field4 ) and exists (select 1 from tab3 where (tab2.field5,tab2.field6)
= (tab3.field5,tab3.field6 ) and tab3.field7 = 'somevalue' )
)

i tried

select field1 from Tab1 , tab2, Tab3 where
tab3.field7 = 'somevalue'
and (tab1.field2,tab1.field3,tab1.field4) = (tab2.field2,tab2.field3,tab2.field4 )
and (tab2.field5,tab2.field6) = (tab3.field5,tab3.field6 )

i also used with comand

with Tab3a as (select tab3.field5,tab3.field6 from Tab3 where tab3.field7 = 'somevalue' ),
Tab2a as (select tab2.field2,tab2.field3,tab2.field4 from Tab2 where
(tab2.field5,tab2.field6) in (select tab3a.field5,tab3a.field6 from tab3a) )
select field1 from Tab1 where (tab1.field2,tab1.field3,tab1.field4) in
(select tab2a.field2,tab2a.field3,tab2a.field4 from Tab2a )


tab1 has 501918 records
tab2 has 5806749
To keep example simple i have used ony 2 subqueries.
My actual query has one more subquery and some of the inner queries have multiple joins

I am using DB2 v9.5. Thanks in advance

Why not write it as a simple join?

Andy

How do you know this?

i tried simple join too
i tried

select field1 from Tab1 , tab2, Tab3 where
tab3.field7 = 'somevalue'
and (tab1.field2,tab1.field3,tab1.field4) = (tab2.field2,tab2.field3,tab2.field4 )
and (tab2.field5,tab2.field6) = (tab3.field5,tab3.field6 )

I ran the query advisor and i have already created Indexes

Query run fast if subquery returns results, but if subquery returns 0 records , whole query takes long time to return 0 records