From: achyl@nospam.infonie.be   
      
   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@nospam.infonie.be   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)