Undocumented Oracle PreparedStatement optimization
We just get a 20% response time gain on a 600+ lines query under Oracle.
Our DBA noticed that queries were faster when launched from SQLDeveloper
than from our JEE application using the JDBC Oracler 11g driver. We
looked at the queries as they actually arrived to the Oracle engine and
they where under the form :
SELECT... WHERE col1 like ':myvar1' OR col2 LIKE ':myvar2' AND col3 IN (:item1,:myvar2,...)
'SELECT... WHERE col1 LIKE ':1' OR col2 LIKE ':2' AND col3 IN (:3,:4,...)
like usual when using PreparedStatement the regular way.
Indeed, every PreparedStatement documentation I'm aware of, beginning
with the one from
states that we have to use
'?' to represent bind variables in
'?' are replaced by
'3' ... by
the JDBC driver. So the database has no way to now in our case that :2
and :4 have the same value. This information is lost.
We discovered that we can use PrepareStatement by providing queries with
named bind variables instead of
'?'. Of course, we still have to set
the right value using the
setXXX(int position,value) setters for every
bind variable occurrence in the query. Then, queries arrive to Oracle
like when using SQLDeveloper, with named bind variables.
OK but what's the deal with all this ?
I'm not sure but I think that this optimization may allow Oracle optimizer to be cleverer, especially for queries with redundant parts. It is especially good for queries with duplicated sub SELECT with IN condition containing all the same list of items. Maybe Oracle create on-the fly WITH clauses or similar optimizations in this case ?
Note that this optimization may only work with Oracle and is probably only useful for very large or redundant queries. I don't recommend it in most cases. AFAIK, neither Hibernate nor Spring-JDBC implements this optimization.