Re: DBMS_STATS

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Tue, 29 Sep 2009 08:25:35 -0700 (PDT)
Message-ID: <318d2f12-4e97-4604-b81d-8dbf6250524c_at_m3g2000pri.googlegroups.com>



Don't confuse Table Level statistics (implicitly including certain Column Level statistics) and Histograms.

Even the first table with Unique values should have statistics gathered. These would update Oracle with information on :

a.  number of rows
b.  size of table (number of blocks to the highwater mark)
c.  min and max values, number of nulls and number of distinct values
in each column.

Thus it is the last set of information that Oracle uses to identify the Customer ID column as unique. (Of course, if you create a Unique Index that is helpful). Furthermore, the min/max and density values are used by Oracle to estimate the number of rows for a given value or for a range scan.

If Histograms are gathered, Oracle uses the Histograms (in addition to all the above information) to estimate the number of rows for a specific value -- ie data skew.

Hemant K Chitale
http://hemantoracledba.blogspot.com

On Sep 29, 10:24 pm, The Magnet <a..._at_unsu.com> wrote:
> On Sep 28, 5:15 pm, John Hurley <johnbhur..._at_sbcglobal.net> wrote:
>
Received on Tue Sep 29 2009 - 10:25:35 CDT

Original text of this message