Oracle translates IN clause

[phiz118]

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?

[pando]

sometimes uses in-list iterator and sometimes OR depends

[phiz118]

Ahh! That makes sense. I was trying to recreate this situation, but I was actually seeing the in list itereator.

Is there a difference processing wise?

[pando]

in-list iterator is supposed to be faster, more advanced

[WilliamR]

in-list iterator is supposed to be faster, more advancedI'd be interested in seeing the documentation link for that.

SQL> SELECT dummy FROM dual WHERE dummy IN ('a','b','c')
2
SQL> @xplan


--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | DUAL | | | |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("DUAL"."DUMMY"='a' OR "DUAL"."DUMMY"='b' OR "DUAL".
"DUMMY"='c')

[pando]

in-list iterator is only used in some cases, if it works for your query then Oracle will never use OR :)


http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opt_ops.htm#1004651

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

[WilliamR]

sometimes uses in-list iterator and sometimes OR dependsIt 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.

[pando]

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

[WilliamR]

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?

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jul 21 14:28:29 2005

SQL> CREATE TABLE testit AS
2 SELECT object_id, object_name FROM all_objects;

Table created.


SQL> CREATE INDEX testit_ix ON testit(object_id);

Index created.

SQL> analyze table testit compute statistics for table for all columns for all indexes;

Table analyzed.

SQL> SELECT COUNT(*) FROM testit WHERE object_id IN (1,2,3)
2
SQL> @xplan

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| TESTIT_IX | 3 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)


SQL> SELECT COUNT(*) FROM testit WHERE object_name IN ('x','y','z')
2
SQL> @xplan

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| TESTIT | 5 | 85 | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_NAME"='x' OR "OBJECT_NAME"='y' OR
"OBJECT_NAME"='z')


SQL> SELECT COUNT(*) FROM testit WHERE object_id = 1 OR object_id = 2 OR object_id = 3
2
SQL> @xplan

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| TESTIT_IX | 3 | 12 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)

[pando]

I dont understand what do you mean exactly. If what you are saying is

in (1, 2, 3)

is same as

1 or 2 or 3

then yes it is correct but in a query plan.

Or you mean in the query plan you see a query is decomposed into several and concatenated at the end?

What I said an in-list is not same as OR in CBO terms. An in-list iterator is not resolved as a in (***) as that using RBO

I think we have some missunderstanding, when I said CBO uses inlist or OR depends I mean they are not resolved the same.

regarding concatenation



explain plan for select * from emp where deptno in (10, 20);

SQL> explain plan for select * from emp where deptno in (10, 20);

Explained.

SQL> @plan

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | CONCATENATION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | | | |
|* 3 | INDEX UNIQUE SCAN | EMP_IDX1 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | | | |
|* 5 | INDEX UNIQUE SCAN | EMP_IDX1 | | | |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("EMP"."DEPTNO"=20)
5 - access("EMP"."DEPTNO"=10)

Note: rule based optimization



Using CBO it always tries to use inlist, at least from 9i upwards

[WilliamR]

Fair enough, the OP did specify 8i which I haven't got for testing on, and it never occurred to me to test on RBO.

I was just making the point that "x IN(y,z)" is the same as, and is rewritten into, "(x = y OR x = z)", in much the same way as "x BETWEEN y AND z" becomes "x >= y AND x <= z", both of which can be seen in Explain Plan output. The OP asked "does Oracle translate the IN clause into OR statements?" and the answer is yes, always - unless you are using RBO which you shouldn't be unless you are still on version 7, which you aren't.

The IN-List Iterator discussion is separate. All it means is the optimizer iterates over the items in an indexed OR list, perhaps repeating some common condition, and (I'm assuming, though the docs don't seem too clear on this) combines rowids at the end. Whether the OR list was arrived at via an IN list or was actually written with OR makes no difference.

Also it is perfectly possible for a query to scan a table once while checking for multiple conditions as it goes, so even in the absence of an IN-List iterator operation an OR list does not necessarily require multiple scans of the same table, which I thought was what you meant by "you are forced to run the queries several times".