If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Oracle translates IN clause

I just wanted a quick confirmation on what I think Oracle 8i is doing. In one of my customer's queries they are specifying an IN list parameter, but when I see the SQL output while running it has been translated into OR statements. So my question is, does Oracle translate the IN clause into OR statements?

When the Optimizer Uses IN-List Iterators
The optimizer uses an IN-list iterator when an IN clause is specified with values, and the optimizer finds a selective index for that column. If there are multiple OR clauses using the same index, then the optimizer chooses this operation rather than CONCATENATION or UNION ALL, because it is more efficient.

It is more advanced because using OR you are forced to run the queries several times, if one of those queries is slow already imagine several. With in-list iterator you just run the query once

It always uses OR. Sometimes that OR can be implemented as an IN-list iterator in which an index is used for each element in the list.

You will notice that there is no index on SYS.DUAL but the IN() list is still converted to a set of OR conditions.

It doesnt always use OR if thatīs the case then there wont be in-list iterator. Now if you say in-list iterator is implemented with OR then probably yes probably not, the point is it is not translated into several OR with in-list iterator. If I remember right inlist has something to do with some loopping mechanism in CBO better than concatating result sets

Regarding DUAL, since there is NO index then of course in-list iterator cannot be used so you see several ORs. If you read the quote I posted it says, a selective index must be present for in-list iterator to work

The IN-list iterator operation simply executes an indexed access for each item in a list, whether specified with IN or OR. The "IN (list)" construction is just a way to let you specify multiple OR conditions without repeating the left-hand expression.

Can you show an example of an IN (list) query not being converted to use OR?

Also can you demonstrate the scenario where an OR condition causes queries to be run several times?