Re: WHERE IN vs. WHERE OR OR OR

Art S. Kagel wrote: > > Daniel Kirkdorffer wrote: > > > > Hi, > > > > Using JDBC against and Informix database I was wondering if anyone > > knew which WHERE clause would be more efficient: > > > > SELECT x FROM y WHERE x IN (1, 2, 3,..., n)> > > > or > > > > SELECT x FROM y WHERE x = 1 OR x = 2 OR x = 3 OR ... OR x = n> > > > (Where x is a primary key) > > > > Would the choice be the same if n > 100, or even n > 1000? > > > > Any suggestions would be greatly appreciated. > > In general an IN clause is more efficient than a set of OR conditions. Just curious, but why? If they are logically the same, what is the optimiser doing representing them differently internally? Does it not analyse multiple OR conditions for common features? I'm not sugesting it should, just interested to find out how sophisticated the optimiser is. > The kicker is that if the table is fragmented, and in a few other > esotheric circumstances the optimizer will expand either into a set of > UNIONs. > > Art S. Kagel -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ ---