Re: JDBC / Oracle : beware the bind variables ?

"Achille Carette" <achyl_at_nospam.infonie.be> wrote in message news:GM6rc.518$yh.277_at_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=en&lr=&selm=3D25557D.E2AC24A8%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
> ------------------------------------------
> achyl_at_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 Received on Thu May 20 2004 - 21:03:40 CEST