Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: lengthy "in" clause performance

    Hello,

    I am porting a java application that works with MySQL so it can work with Oracle as well. One of repeating queries in that application is a long select that looks as follows:

    select distinct(PID) from ABC where PKEY in (val1, val2, val3, ...) or (PKEY>=val1000 AND PKEY <=val1001) or (PKEY>=val1002 AND ...)

    The PKEY column is obviously indexed.
    There are few hundreds of values in the "in" clause and in the range part.
    MySQL seems to deal with this query quite ok. Oracle on the other hand, is imposing a limitation on the number of values in the "in" clause (1000 values), plus - prefers to do full table scans.

    Any idea about how this sort of select may be optimized to work better with Oracle?

    Thanks,
    Saar

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    The way the optimizer handles IN lists varies between Oracle versions. 10g tends to make better informed choices than 9i, and so on.

    Yes there is a limit to the number of elements in an IN list. Also bear in mind that Oracle has a query caching mechanism that I don't think is present in MySQL, so large dynamically generated queries that are only used once are probably rather more expensive in system resources in Oracle than they would be in MySQL (the benefit of course is better performance for reused queries).

    Could you change it to pass an array of some sort, so that the IN(list) could be changed to IN(subquery)?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another approach --
    Code:
    select PID from ABC 
     where PKEY in (val1, val2, val3, ...)
    union
    select PID from ABC
     where PKEY >= val1000 AND PKEY <= val1001 
    union
    select PID from ABC
     where PKEY>=val1002 AND ...
    note DISTINCT no longer necessary, as UNION does the same
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2006
    Posts
    2
    It is not easy to change it to IN (subquery) because the values in the query do not appear anywhere in the database. That means I'll need first to insert them, and after I complete the query delete them. That means I'll need to pay an additional insert and delete. Do you think it may still be more effective? I tend to believe it will make it slower.

    Howcome Oracle preferes not to use the index? that seems very strange to me.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by saarb16
    It is not easy to change it to IN (subquery) because the values in the query do not appear anywhere in the database. That means I'll need first to insert them, and after I complete the query delete them. That means I'll need to pay an additional insert and delete. Do you think it may still be more effective? I tend to believe it will make it slower.

    Howcome Oracle preferes not to use the index? that seems very strange to me.
    No, I meant a subquery using the collection variable passed into the procedure:

    Code:
    SQL> var rc refcursor     
    SQL> 
    SQL> DECLARE
      2      myvar INTEGER_TT;  -- Created in SQL with "CREATE OR REPLACE TYPE"
      3  BEGIN
      4      myvar := INTEGER_TT(1,2,7369,7782,7934);
      5      OPEN :rc FOR
      6          SELECT empno,ename,job,sal
      7          FROM   emp
      8          WHERE  empno IN
      9                 ( SELECT column_value FROM TABLE(myvar) );
     10  END;
     11  /
    
    PL/SQL procedure successfully completed.
    
    
         EMPNO ENAME      JOB              SAL
    ---------- ---------- --------- ----------
          7369 SMITH      CLERK            800
          7782 CLARK      MANAGER         2450
          7934 MILLER     CLERK           1300
    
    3 rows selected.
    
    SQL>
    Oracle can use an index for an IN list. Are the statistics up to date? Would 1000 index searches actually be faster than a table scan?

Posting Permissions

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