Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index not used in LIKE 'aaa%'

Re: index not used in LIKE 'aaa%'

From: Ted Chyn <tedchyn_at_yahoo.com>
Date: 21 Apr 2004 09:07:35 -0700
Message-ID: <44a19320.0404210807.70df94da@posting.google.com>


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...

> > 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 Wed Apr 21 2004 - 11:07:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US