Re: Histograms on Columns for CBO

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 Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Fri Oct 05 2007 - 13:56:35 CDT