Apart from the random amount of time needed, the resulting histograms may be
collected on a very small sample of the data set and could vary their contents
randomly from day to day in terms of identifying popular values and gaps in
ranges.
If you stick with histograms only on indexed columns (having first gathered
stats on all columns size 1) then you are perhaps reducing the risk of randomly
changing execution plans because (a) if Oracle uses the index num_distinct
instead of the products of num_distinct for its columns then the histogram will
be irrelevant and (b) indexes tend to have at least one column that has a large
number of distinct values, and such columns are likely to be the most important
in cardinality calculations and have least variation in the data pattern
implied by their histogram.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Matt Adams [MAdams@xxxxxxxxxxxxxxxxxxx]
Sent: 09 November 2015 18:06
To: oracle-l
Subject: RE: gather stats
I can understand not gathering histograms across all columns due to the
potential time it would take to do so. Is there any other technical reason not
to..(perhaps some issue with the optimizer I'm not currently aware of)?
I routinely gather histogram information for all INDEXED columns.
Matt Adams
--
//www.freelists.org/webpage/oracle-l
