Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: index not used in LIKE 'aaa%'
anurag,
the index hint worked.
there are 5.8 million rows in table and there are 45000 (about 7.6%)
patient_acct with prefix 613 and only 6 of them are like
'613%31713HENSL'.
like you said optimizer probably only use 613 prefix in deciding
whether
to use index or not(any thing after % is ignored).
thanks for your help
ted
"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<TJihc.98559$e17.40042_at_twister.nyroc.rr.com>...
> "Ted Chyn" <tedchyn_at_yahoo.com> wrote in message news:44a19320.0404201449.c18f316_at_posting.google.com...
> --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' > / > > ? > > AnuragReceived on Wed Apr 21 2004 - 11:07:35 CDT