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

Thanks for the info Jonathan. This was a great place for me to have looked at the new v$sql_plan ...

SELECT STATEMENT Optimizer = CHOOSE (Cost = 22 Card = Bytes = )   TABLE ACCESS (FULL) OF T1 Optimizer = ANALYZED (Cost = 22 Card = 1 Bytes = 55)

Richard

Jonathan Lewis wrote:
>
> 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 )
> >>
Received on Wed May 01 2002 - 14:22:03 CDT