Hello all,
I noticed a difference in the explain plans between JDBC using bind
variables (PreparedStatem ent) 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 PreparedStateme nt is more
efficient for "repeatedly " executed queries.
Achille Carette
------------------------------------------
achyl@nospam.in fonie.be
I noticed a difference in the explain plans between JDBC using bind
variables (PreparedStatem ent) 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 PreparedStateme nt is more
efficient for "repeatedly " executed queries.
Achille Carette
------------------------------------------
achyl@nospam.in fonie.be