Re: ** histograms

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_at_yahoo.com> 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

http://www.pythian.com/blogs/author/alex/
http://blog.oracloid.com
--
http://www.freelists.org/webpage/oracle-l