Re: How to speed analyzing histograms

"Kalle" <kalle_at_nospam.com> wrote in message news:flgcf.14258$_k2.236505_at_news2.nokia.com...
> Hi all,
>
> we have 9.2.0.x database and we need to create histograms on weekly basis.
> The problem is that it takes a long time and we would like to speed it up.
> There are many tables with hundreds columns and hundred indexes
>
> Any ideas how to do this,
>
> Thank you in advance....
>
> Kalle
>

In general, only a few columns in your database are likely to need histograms. Think about the application code, and the application requirements, and try to identify columns that

    frequently appear in WHERE clauses
    and have a non-uniform data pattern

        (which could be due to a small subset of
        the possible values being responsible for a
        large fraction of the rows, or could  be due
        to gaps in data ranges, or anything where a
        graph of your data is not a fairly flat, unbroken
        line).

This probably identifies the columns that may benefit from a histogram.

Collecting histograms

    "for all columns"
or

    "for all indexed columns"
is almost invariably a bad idea.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005