Re: How to get Oracle to an Index without hints?

It is possible that your test was deceiving you. EXPLAIN PLAN will always treat something that looks like a bind variable as if it were a character. Typically you either have to put an explicit coercion around it - e.g. to_number(:n1) or depend on actual execution and tracing / stats.

The following test script (which I have just run against 9.0.1) should make the point:

spool c_coerce

drop table t1;
create table t1
nologging
as
select
 to_char(rownum) id,
 lpad(rownum,50,'0') padding
from all_objects;

create unique index i1 on t1(id);

analyze table t1 compute statistics;

variable n1 number(10);
variable v1 varchar2(10);

execute :n1 := 22
execute :v1 := '22'

rem
rem If we try to explain these as they stand, the internal EXPLAIN PLAN rem will treat both binds as strings and use an index. But if we execute rem them, we will see that the numeric one will do far more logical I/o rem than the varchar2 one - demonstrating a tablescan rem

set autotrace traceonly

select * from t1 where id = :n1;
select * from t1 where id = :v1;

set autotrace off

spool off

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Richard Kuhler wrote in message ...


>I remember experiencing a bind typing problem several years ago with

>v7.  Essentially, Oracle would convert all the data values to the bind

>variable type in an attempt to evaluate the criteria (rather than

>converting the bind variable).  I had mentioned to someone to watch out

>for this problem just a couple weeks ago.  However, when I attempted to

>create a simple demonstration of the problem, I couldn't recreate it

>under 8i or 9i.  Did Oracle change the way binds are handled?  Can you

>give an example that demonstrates it under the current versions?

>

>Richard

>

>Jonathan Lewis wrote:

>>

>> One quick thought - are you sure that the

>> types of the bind variables match the types

>> of the columns - in particular the bind variable

>> for

>>     >> WHERE  ( POS_MWBT = :1 )

>>