Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** histograms

Re: ** histograms

From: Alex Gorbachev <>
Date: Sun, 19 Nov 2006 23:55:02 -0500
Message-ID: <>

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 <> wrote:
> 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
> ________________________________
> Sponsored Link
> Mortgage rates as low as 4.625% - $150,000 loan for $579 a month.
> Intro-*Terms

Best regards,
Alex Gorbachev

The Pythian Group
Sr. Oracle DBA
Received on Sun Nov 19 2006 - 22:55:02 CST

Original text of this message