Re: Index Not Used with Parameterized Query

From: VC <boston103_at_hotmail.com>
Date: Mon, 29 Dec 2003 18:40:35 GMT
Message-ID: <Dy_Hb.681933$HS4.4836511@attbi_s01>

Hello,

Here's what I'm getting on Oracle 8.1.7.2 (Solaris):

In sqlplus:

---

Oracle8i Release 8.1.7.2.0 - Production
JServer Release 8.1.7.2.0 - Production

create table t1 as select * from all_objects; create index t1_idx on t1(object_name);

var x varchar2(30)
exec :x:='T%'

alter session set events '10046 trace name context forever, level 12'

select object_id from t1 where object_name like :x

---

... in the raw trace:

---

bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=32 offse
t=0

   bfp=018deda4 bln=32 avl=02 flg=05
   value="T%"

---

.. in the tkprof output:

---

Parsing user id: 20

Rows Row Source Operation

-------  ---------------------------------------------------
     91  TABLE ACCESS BY INDEX ROWID T1
     92   INDEX RANGE SCAN (object id 612256)
==================================================

What's your trace showing ?

VC

"Biff Gaut" <biff_gaut_at_hotmail.com> wrote in message news:a6090148.0312290910.415e9b8e_at_posting.google.com...
> Thanks for the response - sorry for my slow reply, I purposefully left
> the computer home over the holidays!
>
> We are using Oracle 8i. All our queries use trailing '%', but none
> use a leading or embedded '%'.
>
> Biff Gaut
> Gaithersburg, MD
Received on Mon Dec 29 2003 - 12:40:35 CST