Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

do ins become a long list of or

 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
mssql, oracle and db2, so we have to be careful
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic