Re: ** histograms

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: ajoshi977@xxxxxxxxx
  • Date: Mon, 20 Nov 2006 07:51:22 -0700

At 10:49 AM 11/19/2006, A Joshi wrote:
About use of histograms : I think histograms are useful for indexes on columns that are not very selective.

Histograms have nothing to do with indexes. A histogram can be useful to the CBO (the RBO does not use histogram information) if
(a) the column is used in a predicate
(b) the distribution of column values differs significantly from uniform which the CBO assumes in the absence of a histogram

to "prove" the point that histograms have nothing to do with indexing I have an example where a histogram on a non-indexed column reduces cpu time and elapsed time of a query by by 50% and 40% respectively, i.e. the sql runs almost twice as fast with the histogram and consumes half as much cpu compared to without the histogram.

However I came across note 1031826.6 on metalink. About maintenance and space cost. I think space cost is negligible and can be ignored. About maintenance : does it mean statistics need to be gather often? Or does it mean some other cost.

Question : Is there any other overhead or any other negative impact of using histograms?


Is it advisable to use histograms just for some tables and some specific columns or is it OK to just set database wide?


At least in my opinion. I have seen an unnecessary histogram - on a column with perfectly uniform data distribution - destroy ( and I mean destroy, by a factor of ~ 9000 ) the performance of a sql statement. See also the recent post by Fuad Arshad and I quote: "it is generating histograms which doesnt work well with the application".


Wolfgang Breitling
Centrex Consulting Corporation

This email has been scanned by the MessageLabs Email Security System.
For more information please visit ______________________________________________________________________

Other related posts: