Parsing and binding variables for SELECT .. WHERE column IN ( .. ) queries

Question

I'm looking to make sure that all queries in a php web application have proper use of bind variables to minimize parsing of the queries.

I'm wondering how Oracle parses queries that compares a column to a list of values. Will Oracle consider these statements to be the same, or must the list be inside bound variables?

select char from alphabet where char not in ('a', 'b');

select char from alphabet where char not in ('c', 'd');

If the contents of the list must be in a bind variable, can it be done with a single variable, or must one put each item on the list in a separate variable?

select char from alphabet where char not in (:list);

select char from alphabet where char not in (:c1, :c2);

If the latter is true, will queries with a different number of items in the list still be considered to have the same structure?

select char from alphabet where char not in (:c1, :c2);

select char from alphabet where char not in (:c1, :c2, :c3);

Answer 1

You must bind the elements of an IN list, otherwise Oracle will consider these separate statements. Any differences in the text of the SQL will cause a new statement to be parsed (excepting some cases when cursor_sharing = force). Therefore your examples with :a, :b and :a, :b, :c will be considered different SQL statements.

Each element must be bound to a separate variable. If you bind 'fred,nick' to one variable, Oracle will look for the string "fred,nick", instead of the separate items fred and nick.

If you have variable in-lists, but don't want to be parsing a new statement for each additional item, you'll need to pass the elements as a comma separated string into one bind variable, then parse the elements out yourself.

Tom Kyte discusses a couple of ways to do this here. Basically you can do this by writing a function which returns a nested-table which you can wrap a table function around to make it a row source. Alternatively it can be done in pure SQL by using the connect by level <= :num_of_rows trick to generate the number of elements you need and parsing the bind variable that way.