** histograms

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Sun, 19 Nov 2006 09:49:55 -0800 (PST)
Message-ID: <20061119174955.2021.qmail@web58008.mail.re3.yahoo.com>

Hi,

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 histograms?

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