On Oct 5, 2:56 pm, Cristian Cudizio <cristian.cudi..._at_yahoo.it> wrote:
> On 5 Ott, 20:42, Niall Litchfield <niall.litchfi..._at_dial.pipex.com>
> wrote:
>
> > zigzag..._at_yahoo.com wrote:
> > > I have a packaged application (I cannot modify source code). There are
> > > several queries which use upper(reportedby) like '%JOE%' in where
> > > clause, I created function based index on upper(reportedby), however
> > > fb index does not get used. I was wondering whether it is possible to
> > > create histograms to influence CBO.
>
> > like '%string%' will not benefit from an index. like 'string%' will
> > using b-trees to satisfy full text case insensitive search won't work
> > for you.
>
> > --
> > Niall Litchfield
> > Oracle DBAhttp://www.orawin.info/services
>
> As suggested you can see on AskTom he suggests to use oracle text, but
> on your case, with a packaged
> application, it that suggestion is not applicable. On the other hand
> as said by Niall with expression "like '%substring%"
> it is difficult to being helped by an index; maybe that this is your
> only problem and as suggested by
> Niall if you hava expessions "like 'substring%' your fb index will be
> used. If your
> application automatically puts the "%" before the string i suspect
> there is no hope.
>
> regards,
> Cristian Cudiziohttp://oracledb.wordpress.comhttp://cristiancudizio.wordpress.com
Thanks a lot to all of you.
I know like perfers to use full table scan in particular %string% but
I was hoping it will do full index scan (whatever it is called in
CBO )
I think I should be able to force use of fb index thorugh a stored
outline. I have to fisrt set cursor_sharing=force. Need to test it
well in test env.
Received on Fri Oct 05 2007 - 15:06:24 CDT
