aspose file tools*
The moose likes JDBC and the fly likes do ins become a long list of or Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "do ins become a long list of or" Watch "do ins become a long list of or" New topic
Author

do ins become a long list of or

Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

sorry but this is impossible to search upon, but does an IN get expaned out to a long list of ORs?
And is there a performance diffeference?
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

please hurry i am meant to be coding after my lunch break finishes.
(falls off the chair at her own hilarious joke)
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I'd say it is equivalent to a list of ORs. The exact handling depends on the database probably. For example, Oracle would probably take all the values into account when guessing cardinality of the (sub)query, possibly creating a different plan if you use just a few values in the IN cluase, versus a few hundred.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1716
    
  14

According to Tom Kyte's book "Troubleshooting Oracle Performance" (p 386-387), each comparison gets treated separately via an "INLIST ITERATOR" operation i.e. it's handled as if it were a set of "OR col = value" conditions (his examples are searching on indexed columns, so they are actually looking in the index).

So on Oracle at least, it looks like IN does turn into a bunch of ORs (even if IN is still much easier to write/read).

I never knew that!

PS: I think this is specific to "IN (val1, val2, ...)" comparisons , as I think Oracle will try to be smart when dealing with "IN ( sub-query )" e.g. using hashes etc, and there are often ways to make your sub-query much more efficient anyway.


No more Blub for me, thank you, Vicar.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Oh dear, I wrote such a mess once again!

Chris has it right, of course, ORs and IN are equivalent. I suppose that is true not only in Oracle, but in most databases, after all the two things lead to the same result.

In my previous post, I was elaborating on the difference between having a few and a lot of values in the IN clause. Clearly, that has no influence on decisions between IN and OR.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

see i am deciding between getting our sql generator to produce a possibly very long list of ORs (reusing existing code) or adding in IN(?,?,? ...).


there could be 100 items in the list.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Beware there could be some limit. Oracle allows at most 1000 items in the IN list. Other databases could have limitations too, including a limit on the length of the SQL query. And I've a few times triggered the dreaded ORA-600 error (internal error) by submitting a long (and complicated) query to Oracle.

(What database are you on?)
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

mssql, oracle and db2, so we have to be careful
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1716
    
  14

If you have a lot of "OR" conditions, you might consider breaking them into separate SELECTs to eliminate the ORs and make the alternatives easier to read/write/generate, then "UNION ALL" the results within a single SQL statement. But how this performs will depend on your DB and the rest of your query conditions.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: do ins become a long list of or