Hi

I have the following sql:
Select count(*) from ADU.UTADU_ADRESSE where (wvrd_wlgs, wvagentur) = (1,1) or (wvrd_wlgs, wvagentur) = (2,2)

Can the OR-Operator be replaced with the IN-Operator for this query ?

Marc

Which DB2 version supports this type of syntax?

I work with udb 7.1 on Windows / Unix

You can do something like (or use with to generate "temp" table)

Select count(*) from ADU.UTADU_ADRESSE where
(wvrd_wlgs, wvagentur) in ( values (1,1), (2,2))

Could you please let us know in performance point of view, which one is better?

Thanks

Performance:

Test-Statements:
- OR:
Select count(*)
from ADU.UTADU_ADRESSE
where (wvrd_wlgs, wvagentur) = (94,640) or (wvrd_wlgs, wvagentur) = (88,137)

IN:
Select count(*)
from ADU.UTADU_ADRESSE
where (wvrd_wlgs, wvagentur) in ( values (94,640), (88,137))

Table ADU.UTADU_ADRESSE : 5'281'216 records
Index on columns: wvrd_wlgs, wvagentur

--------------------------------------------------------------

I explained these statements by visual explain and got:
Costs:
- OR: 58'000
- IN: 150

I issued the SQL-statement and got time in seconds:
- OR: 10s
- IN: 0,5s

I knew that IN is better than OR, but I thought that UDB does SQL-Rewrite in this case, this difference really surprises me !!!
(if I have only 1 column, then I get the same costs for OR and IN, but with 2 columns like above results a big difference)


Marc

I would make sure you have the latest fixpak installed. If that does not fix the problem, I would change the query optimization for this query:
SET CURRENT QUERY OPTIMIZATION 7 (the default is usually set to 5).