Re: Histograms Used to Select Best Index, or Only Between Index and FTS?

Hello,

Thanks to all who took the time to respond to my original question.

To summarize (Please confirm whether I am correct)

-If the distribution of the data is skewed, histograms *may* influence the optimizer to choose a different index than it would if an even distribution of data is assumed (No histograms). The claim that histograms may only help the optimizer choose between an FTS and an index search, and not between two competiting indexes, is not true.

-Because of the time needed to generate histograms and the extra load they add to the SGA and other resources, one should only generate histograms for tables in which there is a skewed ditribution for which histograms may help.

-In general, histograms will be used more often on a DSS system than an OLTP one. However, a query whose long runtime is causing issues for users in an OLTP system may benefit from histograms if the data that it is accessing has a skewed distribution and there are indexes that can better take advantage of the skewed distribution than the ones chosen by the optimizer.

Thanks,

SAP BASIS Consultant

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<LULZ8.38262$Hj3.115441_at_newsfeeds.bigpond.com>...
> Hi Herman,
>
> I generally don't like generalisations :)
>
> IMHO, whether on not you generate histograms depends not so much on the
> environment (OLTP, DSS, Hybrid, whatever) but more so on whether the
> histograms offer any benefit. If they offer a benefit, use them if they
> don't, well don't.
>
> In OLTP environments it's less likely they'll be useful as accesses are
> often through PKs, queries are smaller and bind variables are the norm.
>
> In DSS the reverse is often the case.
>
> However, if a column is *not* used in a where clause or the column has *even
> distribution* of values, then histograms are redundant (no matter the
> environment) And as well as the issues raised by JL, the gathering of these
> statistics is expensive.
>
> So I always question the generating of histograms if there is no
> justification for doing so.
>
> Richard
> "Herman de Boer" <h.de.boer_at_itcg.nl> wrote in message
> news:ah6ao4$21k$1_at_news1.xs4all.nl...
> > I should have been more specific.
> >
> > For DSS kind-of-databases, histograms on all columns
> > for OLTP kind-oif databases: only if necessary. Agree with mr. Lewis.
> >
> > Kind Regards,
> >
> > Herman de Boer
> > sr consultant
> > IT Consultancy Group bv.
> >
> > Jonathan Lewis wrote:
> >
> > >
> > >In general I advise quite strongly against having
> > >histograms on all columns.
> > >
> >
Received on Fri Jul 19 2002 - 09:14:16 CDT