RE: Histogram ENDPOINT_ACTUAL_VALUE empty using DBMS_STATS

Karen,
AFAIK this is *not* a bug. the endpoint_actual_value is only derived and
stored in cases where Oracle might need it. DBMS_STATS is "enhanced for
performance" compared with ANALYZE; ANALYZE did way too many things
inefficiently in the first place, which is why it is deprecated as a method
to collect optimizer statistics.

additions/corrections welcome,

Kind regards,
Lex.

-------------------------------
visit http://www.naturaljoin.nl
-------------------------------
skype me <callto://lexdehaan>

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Karen Morton
Sent: Friday, September 03, 2004 01:20
To: oracle-l@xxxxxxxxxxxxx
Subject: Histogram ENDPOINT_ACTUAL_VALUE empty using DBMS_STATS

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.


-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf


---
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe 
To read recent messages - http://freelists.org/archives/oracle-l/09-2004

Other related posts: