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 advanced

I'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...ps.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 depends

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.

[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