Oracle translates IN clause
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Oracle translates IN clause

Hybrid View

  1. #1
    Join Date
    Jul 2005
    Posts
    2

    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?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    sometimes uses in-list iterator and sometimes OR depends

  3. #3
    Join Date
    Jul 2005
    Posts
    2
    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?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    in-list iterator is supposed to be faster, more advanced

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    in-list iterator is supposed to be faster, more advanced
    I'd be interested in seeing the documentation link for that.

    Code:
    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')

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Quote Originally Posted by WilliamR
    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
    Last edited by pando; 07-21-2005 at 03:02 AM.

  9. #9
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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?

    Code:
    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)

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width