Varying IN lists – part III
March 19, 2007 Leave a comment
OK, so the varying in list solution is not working for binds where the length > 4000 characters and ODP.NET does not seem to support Oracle collections to the same extent as JDBC (although some results on a Google search indicate that this may have been present in a Beta release but withdrawn). What are the alternatives?
We can return to the previous situation with many similar SQL statments with different length IN lists stuffing up the shared pool.
We could use a routine just before the select in the application to insert the previous IN list argument values into a GLOBAL TEMPORARY TABLE and then change the SQL so that the IN list subquery selects from that. It’s not nice, it’s not the ideal solution but it’s a possibility. An alternative is to the GTT is to use a global variable in a package. Not dissimilar to the GTT approach. Both have a nasty “smell” about them.
In both cases, the general approach is that the application would first have to insert values into the GLOBAL TEMPORARY TABLE or into the global packaged variable (either individual values or in the latter, using the ODP.NET support for associative arrays). Then we would write a query that would have a subquery to either select from the GTT or call a function which would change the associative array to a SQL type.
It sounds a bit daft whichever the method.
(Incidentally I lose track of what I’m meant to call things. Associative arrays used to be called index-by tables which used to be called PLSQL tables. These are part of the Oracle collections framework which includes SQL User Defined Types (UDTs)? That latter are what we need to use in SQL using the TABLE and CAST operators and those are what ODP.NET does not support.)
An initial runstats examination shows that the packaged variable approach is more scalable. This is mainly because a packaged variable is a memory structure whereas inserts into a GTT generate some redo and so runstats picks up on that. In terms of clock speed, the packaged variable approach seems slightly faster.
Hopefully, I will expand this a bit later with proper example code.