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)

Congratulations to the winners

I received just an email from Iggy Fernandez. My answer was not in the set of winners. Congratulations to all three. The challenge might be nice rehearsal to solve using Spatial Network Data Model SQL extension. The email as received:

The Second International NoCOUG SQL Challenge was published on 2/13/11 in the February 2011 issue of the NoCOUG Journal (http://bit.ly/gVNZsW). SQL commands to create the data were provided at http://bit.ly/g58WVn. The challenge was to find the secret message hidden in a seemingly random collection of words. The winners are Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia.) Each winner will receive an Amazon Kindle from contest sponsor Pythian and the August Order of the Wooden Pretzel in keeping with the pronouncement of Steven Feuerstein that“some people can perform seeming miracles with straight Es-Cue-El, but the statements end up looking like pretzels created by somebody who is experimenting with hallucinogens.”

The full announcement can be read in the 100th issue of the NoCOUG Journal (http://bit.ly/rC2gRA).

How many function calls and sysdate evaluated

Talking here about function calls and when sysdate is evaluated. Starting with a silly function f2 that takes a second to execute and a session that shows only the seconds out of a date type.

SQL> alter session set nls_date_format='ss';

Session altered.

SQL> create or replace function f2( x in date ) return date
  2    as
  3    begin
  4            dbms_lock.sleep(1);
  5            return x;
  6    end;
  7  /

Function created.

SQL> select a a, a b from (select f2(sysdate) a from dual);

A  B
-- --
37 37

Elapsed: 00:00:02.02

Written a query that uses the function once. And seems like sysdate is evaluated at the main query and it returns the same value for both a and b. But how come the execution time is two seconds? From the optimizer trace can be seen that the query is transformed to have two calls to the function. That explains the execution time.

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "RAFU"."F2"() "A","RAFU"."F2"() "B" FROM "SYS"."DUAL" "DUAL"

SQL> select f2(sysdate) a, f2(sysdate) b from dual;

A  B
-- --
39 39

Elapsed: 00:00:02.02

Similar result. Lets change the function to have its own cursor.

SQL> create or replace function f3 return date
  2    as
  3    x date;
  4    begin
  5            dbms_lock.sleep(1);
  6            select sysdate into x from dual;
  7            return x;
  8    end;
  9  /

Function created.

SQL> select a a, a b from (select f3() a from dual);

A  B
-- --
42 43

Elapsed: 00:00:02.02

Execution time the same and the result changed. The sysdate is evaluated at the time cursor in the function is called. Remember the query transformation. The transformation may be avoided and the function is called actually only once.

SQL> select a a, a b from (select /*+no_merge*/ f3() a from dual);

A  B
-- --
54 54

Elapsed: 00:00:03.01

Be aware query transformations may lead to changing query results when function are mixed in a query. How about transaction isolation. Does that have influence on the issue when the function is called twice. So is the sysdate behaving like changing rownum. The changing rownum is talked in Laurent Schneider blog and comments there.

SQL> set transaction read only;

Transaction set.

Elapsed: 00:00:00.00
SQL> SELECT F3() A,F3() B FROM DUAL;

A  B
-- --
03 04

Elapsed: 00:00:02.01
SQL> rollback;

The sysdate is not respecting the read only setting on the transaction but it is getting its value just when asked.