Varying IN lists – part II
March 19, 2007 2 Comments
I have blogged before about a solution to varying in lists being issued by an application.
The problem related to hard parsing – when these varying in lists used literals rather than binds (resolved by the cursor_sharing paramter) – and to shared pool usage – with every distinct number of binds in an in list requiring a shared pool entry (or parse if absent therefrom).
Having gently guided the application developers down the path of this type of approach, I was feeling pretty pleased with progress. However, two problems have subsequently presented themselves which underline why the initial approach was taken and why the standard solutions approach is not a silver bullet in all circumstances.
One of the applications concerned uses ODP (Oracle Data Provider for .NET). This is one of those situations where my understanding (or lack thereof) leaves me feeling very vulnerable.
ODP sits on top of OCI in a way that is, at this time, beyond my comprehension in terms of driver internals.
The varying in list “design pattern” uses a single delimited string as a single bind variable. As a result, no matter the length of the single bind, there is only a single statement per x binds for that base bit of SQL.
However, OCI has a limt of 4000 for a VARCHAR2 argument. Therefore, depending on the length of each argument in the delimited string, we are severely limited in the number of arguments that can be passed in.
This brings me back to an old bug raised against the current application. In the old varying IN list approach, the application was limited to something like 1000 arguments in the IN list. There are obvious question marks against the application design of any application which is dynamically building such IN lists – where are the arguments in the IN lists coming from? What is the application object to relational mapping such that this is happening? Can we present alternative structures / entities to avoid these scenarios? etc.
However, by pushing the varying IN list solution, the limit of 1000 arguments is now considered a “nice-to-have”. Because at 8 digits, the maximum number of arguments is now closer to 250!
As the length of the single bind variable reaches the 4000 limit, the error raised is “ORA-01460: unimplemented or unreasonable conversion requested”. We have tried a LONG and CLOB alternative and both are met with the same result (although an erroneous implementation of a workaround can never be ruled out).
One of the frustrations here is that the application developers would love to use a more appropriate interface. The natural choice, IMHO, is to provide some sort of array of the arguments concerned. If we were using JDBC, then using proper Oracle collections (CREATE TYPE…. AS TABLE OF) and the appropriate JDBC structures would be fantastic.
However, for some reason, ODP does not yet support the same functionality. According to the documentation, ODP.NET supports associative arrays/index-by tables/plsql tables but not SQL types. Which raises the question, why has this functionality been absent from ODP.NET for so long?