Histogram ENDPOINT_ACTUAL_VALUE empty using DBMS

From: Karen Morton <karen.morton_at_hotsos.com>
Date: Thu, 2 Sep 2004 17:19:35 -0700
Message-ID: <000401c4914b$b4827600$6401a8c0@Dumbledore>

I was doing some histogram testing and came across something I couldn't explain nor could I find anything on Metalink about it and was hoping someone could assist.

Here are the specifics:
9.2.0.4 on Windows XP
table with 1 million rows
county column (varchar2(30)) has 11 distinct values data values in the column are distributed as follows:

COUNTY                 COUNT(*)


-------------------- ----------

ANDERSON                  50000
BROWN                     25000
CANNON                    25000
DAVIDSON                  50000
FAYETTE                   50000
GORDON                    25000
HARRISON                  25000
LANCOME                   25000
MULTNOMAH                 25000
SMITH                    100000
WASHINGTON               600000

I collect a histogram on the column using dbms_stats as follows: exec dbms_stats.gather_table_stats (user,'my_table', method_opt => 'for columns county')

The resulting information shown in dba_histograms is: Name Endpoint # Endpoint Value Endpoint Actual Value

COUNTY       50000         339086732722891000000000000000000000
COUNTY       75000         344361036295129000000000000000000000
COUNTY       100000        349208450178451000000000000000000000
COUNTY       150000        354401379301588000000000000000000000
COUNTY       200000        364786209484552000000000000000000000
COUNTY       225000        370261905164842000000000000000000000
COUNTY       250000        375170252614487000000000000000000000
COUNTY       275000        395939118500926000000000000000000000
COUNTY       300000        401536910355255000000000000000000000
COUNTY       400000        432528194537519000000000000000000000
COUNTY       1000000       453054781625798000000000000000000000

Note that the ENDPOINT_ACTUAL_VALUE column is null.

I then collect the histogram on the column using ANALYZE as follows: ANALYZE TABLE my_table COMPUTE STATISTICS FOR COLUMNS county ;

The resulting information shown in dba_histograms is: Name Endpoint # Endpoint Value Endpoint Actual Value

COUNTY       50000         339086732722891000000000000000000000
ANDERSON
COUNTY       75000         344361036295129000000000000000000000   BROWN
COUNTY       100000        349208450178451000000000000000000000   CANNON
COUNTY       150000        354401379301588000000000000000000000
DAVIDSON
COUNTY       200000        364786209484552000000000000000000000
FAYETTE
COUNTY       225000        370261905164842000000000000000000000   GORDON
COUNTY       250000        375170252614487000000000000000000000
HARRISON
COUNTY       275000        395939118500926000000000000000000000
LANCOME
COUNTY       300000        401536910355255000000000000000000000
MULTNOMAH
COUNTY       400000        432528194537519000000000000000000000   SMITH
COUNTY       1000000       453054781625798000000000000000000000

WASHINGTON Using ANALYZE the ENDPOINT_ACTUAL_VALUE column is now populated as I expected it to be.

Not that this is a big deal, but it is annoying. So, my question is this: Why doesn't the ENDPOINT_ACTUAL_VALUE column get populated when using dbms_stats? Looks like a bug to me but I couldn't find anything to confirm.

Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com <http://www.hotsos.com/> Upcoming events at http://www.hotsos.com/education/schedule.html

---

To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To read recent messages - http://freelists.org/archives/oracle-l/09-2004 Received on Thu Sep 02 2004 - 20:32:58 CDT

Histogram ENDPOINT_ACTUAL_VALUE empty using DBMS_STATS