Re: Index Usage ?!

Hi!

> 25-07-2003 18:46:51, Irfan Khan <irfan.khan_at_converge.com> wrote:
> >From what i understand when u analyze , use the keyword of size to set
the
> >histogram buckets
> >Generally it should be no of distinct values in a column + som ball park
no.
> >say ten.

Max nr. of buckets is 254 per column, thus in some conditions you can't have a bucket for each value.
But more importantly, histograms aren't used with queries using bind values since during next query the bind variable could be set to completely different value and in order to compute a more appropriate execution plan we would have to go through the most expensive parse phase again.

Also, when not using bind variables (which is a bad idea in OLTP because additional parses), using histograms makes each individual parse (which involves analyzing predicates on histogrammed columns) even slower.

OTOH, histograms are good for processing the data in large quantities, but bad or even unusable in high concurrency OLTP environments. Of course maybe 10G already has some kind of pre-parsing enchancements where multiple execution plans are calculated for each possible bind variable value range (bucket). We'll see ;)

Tanel.

> >U can play around with this analyzing with histograms.
> >I tried and it worked for me and so it should for you as well.
> >I will do more research and will get back to you.
> >Irfan , Khan
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Prem Khanna J
> INET: jprem_at_kssnet.co.jp
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri Jul 25 2003 - 08:21:06 CDT