Re: ** histograms

  • From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
  • To: ajoshi977@xxxxxxxxx
  • Date: Sun, 19 Nov 2006 23:55:02 -0500

I would first question existence of not very selective indexes. Or
those are columns of multi-column index that is selective?

On 11/19/06, A Joshi <ajoshi977@xxxxxxxxx> wrote:
  About use of histograms : I think histograms are useful for indexes on
columns that are not very selective. 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

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

From  metalink note 1031826.6 :
Histograms are stored in the dictionary and computed by using the ANALYZE
command on a particular column. Therefore, there is a maintenance and space
cost for using histograms. You should only compute histograms for columns
which you know have highly-skewed data distribution.

When to Not Use Histograms

Also, be aware that histograms, as well as all optimizer statistics, are
static. If the data distribution of a column changes frequently, it is
necessary to recompute the histogram for a given column. Histograms are not
useful for columns with the following characteristics:

  o all predicates on the column use bind variables
  o the column data is uniformly distributed
  o the column is not used in WHERE clauses of queries
  o the column is unique and is used only with equality predicates

Sponsored Link

 Mortgage rates as low as 4.625% - $150,000 loan for $579 a month.

Best regards,
Alex Gorbachev

The Pythian Group
Sr. Oracle DBA

Other related posts: