Variable inlist

There comes every now and then discussions about how to pass varying in list to a SQL statement as a single bind. Just currently there is such discussion going on at oracle-l mailing list "Large IN LIST in an OBIEE query". Also several discussions may be found in Oracle discussion forums.

One problem with these varying in lists is that the SQL query is different for every amount of values passed to an in list.

where col in (?,?);


where col in (?,?,?);

Another problem is that an in list accepts only 1000 different values. This may be overcome using OR:

where col in (?,?,...,?) --1000 placeholders
       or col in (?,?,...,?) --1000 placeholders
       or col in (?,?,?);

But that is just plain old stupid. It seems to be the thing that comes to a developers mind first. As the approach is seen too often.

All kinds of single string passing approaches can be found using Google. With those there is an issue of maximum length 4000 bytes of varchar2 datatype.

One approach is to create an temp table, populate that before executing a query and using the temp table as a normal table in the query. Problems: Two round trips to the database. How to populate the temp table efficiently.

It was two years ago I attended Tanel Poder Advanced Oracle Troubleshooting Seminar. He mentioned an approach using SQL Collection types and table function to implement the varying inlist with just one bind. Actually seems like he is having his seminar starting just todayhttp://blog.tanelpoder.com/seminar/. Lucky you attending. This week I will have a pleasure to attend Jonathan Lewis seminar in Helsinki.

Just a bit earlier I wrote about how to populate a table efficiently using Java. This approach may be used to populate the temp table. But why do that when you can do it all in a single call as Tanel described.

create type numbers is table of number;

And Map the NUMBERS type to ArrayDescriptor.createDescriptor.

        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("NUMBERS", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
          = (OraclePreparedStatement)c.prepareStatement(
                 "select /*+gather_plan_statistics*/ count(*) cou"+
                 "   from inlist_test "+
                 "  where num in ( " +
                 "        select column_value " +
                 "          from table(?))");
        ops.setARRAY(1, a);

        ResultSet rs = ops.executeQuery();

This approach works also for another types of Collections Dates, Strings and so . Just create own table of type for each needed. Readily availabel SYS.ODCINUMBERLIST seems to be safe to use with Collections smaller than about 32k. So no additional database objects need to be greated.

ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST", c);

One problem in the discussions have been how the optimizer sees the cardinalities of the collection. Here are three different plans from 11.2.0.2 depending on the Collection and table size. As you can see quite large collections may be passed to the database using this technique. Plan 1497893883 is generated using one million numbers in the inlist. With large Collections be aware of the memory consumption I mentioned in the using Java post.

SQL_ID  fzb8ysr291xz3, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(*) cou   from inlist_test   
where num in (         select column_value           from table(:1 ))
 
Plan hash value: 4131715231
 
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |      1 |00:00:00.01 |       3 |       |       |          |
|   1 |  SORT AGGREGATE                     |             |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|*  2 |   HASH JOIN SEMI                    |             |      1 |      1 |     24 |00:00:00.01 |       3 |  1517K|  1517K| 1308K (0)|
|   3 |    TABLE ACCESS FULL                | INLIST_TEST |      1 |     99 |     99 |00:00:00.01 |       3 |       |       |          |
|   4 |    COLLECTION ITERATOR PICKLER FETCH|             |      1 |     25 |     25 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------
 
 
 
Plan hash value: 1497893883
 
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |      1 |00:00:01.15 |    1528 |   1262 |       |       |          |
|   1 |  SORT AGGREGATE                     |             |      1 |      1 |      1 |00:00:01.15 |    1528 |   1262 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI              |             |      1 |      1 |    999K|00:00:04.22 |    1528 |   1262 |    34M|  6431K|   48M (0)|
|   3 |    COLLECTION ITERATOR PICKLER FETCH|             |      1 |   1000K|   1000K|00:00:00.71 |       0 |      0 |       |       |          |
|   4 |    TABLE ACCESS FULL                | INLIST_TEST |      1 |    893K|    999K|00:00:00.69 |    1528 |   1262 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------
 

 
Plan hash value: 2750379354
 
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      1 |00:00:00.45 |    1528 |   1262 |       |       |          |
|   1 |  SORT AGGREGATE                      |             |      1 |      1 |      1 |00:00:00.45 |    1528 |   1262 |       |       |          |
|*  2 |   HASH JOIN                          |             |      1 |   3125 |  99999 |00:00:00.44 |    1528 |   1262 |  3749K|  1936K| 5625K (0)|
|   3 |    SORT UNIQUE                       |             |      1 |    100K|    100K|00:00:00.11 |       0 |      0 |  4588K|   893K| 4078K (0)|
|   4 |     COLLECTION ITERATOR PICKLER FETCH|             |      1 |    100K|    100K|00:00:00.07 |       0 |      0 |       |       |          |
|   5 |    TABLE ACCESS FULL                 | INLIST_TEST |      1 |    893K|    999K|00:00:00.68 |    1528 |   1262 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("NUM"=VALUE(KOKBF$))
 
Note
-----
   - dynamic sampling used for this statement (level=2)