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: Histograms on Columns for CBO

Re: Histograms on Columns for CBO

From: joel garry <joel-garry_at_home.com>
Date: Fri, 05 Oct 2007 14:58:56 -0700
Message-ID: <1191621536.175791.196420@r29g2000hsg.googlegroups.com>


On Oct 5, 1:06 pm, zigzag..._at_yahoo.com wrote:
> 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

There's fbi optimizer bugs in later versions (like 10.2.0.2.). Don't know about 9206 and your specific issue, but be warned messing around here may bite you with every patch, not to mention the cursor_sharing is a large hammer.

Also see http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#51111

jg

--
@home.com is bogus.
"IMHO, any perf turning approach must yield accurate and reproachable
results using a clearly defined tools and steps." - fmhabash
Received on Fri Oct 05 2007 - 16:58:56 CDT

Original text of this message

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