Re: index not used in LIKE 'aaa%'

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 20 Apr 2004 23:53:23 GMT
Message-ID: <TJihc.98559$e17.40042@twister.nyroc.rr.com>

"Ted Chyn" <tedchyn_at_yahoo.com> wrote in message news:44a19320.0404201449.c18f316_at_posting.google.com...
> anurag,
>
> I tried set optimize_index_cahing and cost_adj and it did not help.
> even index hint did not work and the only thing worked is to use
> rule hint see below.
>
> There are a big difference in terms of performance.
> ====
>
> 1 select /*+ rule */ patient_acct,pi_surr_id from pymtitem
> 2* where patient_acct like '613%3713HENSL'
> SQL> /
>
> 6 rows selected.

--snip--

I did suggest other things like finding out how many rows were returned using query "patient_acct like '613%'".
If rows were very less then you can try creating histogram (although its efficacy would depend on distribution of values).

And lastly: I'm quite surprised when you say that index hint was ignored by oracle? For your query, I would not expect the index hint to be ignored. Are you sure you put that hint correctly?

select /*+ index(pymtitem PAYITM_PACCT_IDX' ) */ patient_acct,pi_surr_id from pymtitem where patient_acct like '613%3713HENSL'
/

Anurag Received on Tue Apr 20 2004 - 18:53:23 CDT