RE: gather stats

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
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Matt Adams [MAdams@TroverSolutions.com]
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
--