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: <zigzagdna_at_yahoo.com>
Date: Fri, 05 Oct 2007 15:10:26 -0700
Message-ID: <1191622226.365340.274210@22g2000hsm.googlegroups.com>


On Oct 5, 5:58 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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 seehttp://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimo...
>
> jg
> --
> @home.com is bogus.
> "IMHO, any perf turning approach must yield accurate and reproachable
> results using a clearly defined tools and steps." - fmhabash- Hide quoted text -
>
> - Show quoted text -

Thanks,
I am planning to move to 9208 and then use cursor_sharing=force. cusror_sharing=force makes use of Stored Outlines more attractive otherwise most of the statements are different (because no bind vars are being used in packaged application). Received on Fri Oct 05 2007 - 17:10:26 CDT

Original text of this message

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