JDBC / Oracle : beware the bind variables ?

[Achille Carette]

Hello all,

I noticed a difference in the explain plans between JDBC using bind
variables (PreparedStatement) and SQLPlus for the same query. The query made
through JDBC using bind variables makes a full table scan, while the query
made in SQLPlus, replacing the "?" by string literals uses an index.
(Platform: Oracle 10.1 / Linux RedHat 9 / JDK 1.4.2 / Oracle 10g thin JDBC
driver )

As a common practice is to write and optimize queries using SQLPLus or Toad
then to replace the literals by bind variables in the Java code, the actual
performance of the queries may be lower than expected.

I found a good explanation of the reason:
"The cost based optimizer (that 's what we 're talking about, not) makes its
choices based on the availability of indexes (among other objects), and the
distribution of values in the indexes (how selective the index will be for a
given value). Obviuosly, when working with bind variables, the suitability
of the index from a distribution point of view is harder to determine. The
optimizer has no way to determine beforehand to what value matches will be
sought. This might (should) lead to another execution plan. No surprise
here, as far as I am concerned."
[
http://groups.google.com/groups?hl=e...C24A8%40hp.com ]

In the situation i ran into, even a hint didn't correct the problem - i had
to avoid using bind variables.

This is completely opposed to the common idea that PreparedStatement is more
efficient for "repeatedly" executed queries.

Achille Carette
------------------------------------------
ac***@nospam.infonie.be

[Jim Kennedy]

"Achille Carette" <ac***@nospam.infonie.be> wrote in message
news:GM**************@amsnews02.chello.com...

Hello all,

I noticed a difference in the explain plans between JDBC using bind
variables (PreparedStatement) and SQLPlus for the same query. The query made through JDBC using bind variables makes a full table scan, while the query
made in SQLPlus, replacing the "?" by string literals uses an index.
(Platform: Oracle 10.1 / Linux RedHat 9 / JDK 1.4.2 / Oracle 10g thin JDBC
driver )

As a common practice is to write and optimize queries using SQLPLus or Toad then to replace the literals by bind variables in the Java code, the actual performance of the queries may be lower than expected.

I found a good explanation of the reason:
"The cost based optimizer (that 's what we 're talking about, not) makes its choices based on the availability of indexes (among other objects), and the distribution of values in the indexes (how selective the index will be for a given value). Obviuosly, when working with bind variables, the suitability
of the index from a distribution point of view is harder to determine. The
optimizer has no way to determine beforehand to what value matches will be
sought. This might (should) lead to another execution plan. No surprise
here, as far as I am concerned."
[
http://groups.google.com/groups?hl=e...C24A8%40hp.com ]
In the situation i ran into, even a hint didn't correct the problem - i had to avoid using bind variables.

This is completely opposed to the common idea that PreparedStatement is more efficient for "repeatedly" executed queries.

Achille Carette
------------------------------------------
ac***@nospam.infonie.be

Yes. for example lets say you did
select * from emp where sex='F';
and there were a million emp records and only 5 of those meet the condition.
(and the tables are analyzed) then the index on sex would be faster in the
above case than if you were looking for 'M'.(assuming a not null condition)
In this special case bind variables would solve it generically and would
probably do a full table scan. In fact in datawharehousing it usually is
preferable to not use bind variables. In OLTP it is highly preferable to
use bind variables. In one version of Oracle , and I thought 10G would have
it, it will do bind variable peeking. (still on 8.1.7.4, so I haven't
looked into it.)\

So usually it is much better to use bind variables especially in an OLTP
application. Also a full table scan is not necessarily bad.

Jim