RE: SQL to indicate existance of at least one line

Build a radix index over PRICE column. Use VE on it to see if the picture
changes.
Build an EVI over PRICE column. Use VE on it to see if the picture changes.

As for which statement to use... I prefer simple, which in this case is the
2nd statement. So does query optimizer, most of the time anyway.
You might want to append OPTIMIZE FOR 1 ROW, though that should not be
necessary.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL to indicate existance of at least one line

Well, now I'm running a request against a field that I know isn't indexed
anywhere :

1. select '1' from sysibm.sysdummy1 where exists( select * from mytable
where price between 1 and 2)
2. select '1' from mytable where price between 1 and 2 fetch first row only

VE gives virtually identical explication and identical times for a 20million
line table.

The first example is actually more involved because of a join with
sysdummy1. In both cases a temporary table is created with the lines that
fulfil the search criteria.