Contents of DBA_HISTOGRAMS according to the ANALYZE cmd used

From: Spendius <spendius_at_muchomail.com>
Date: 31 Jan 2005 00:53:57 -0800
Message-ID: <aba30b75.0501310053.6d9f3634@posting.google.com>

Here is my table (all types are NUMBER): SQL> desc sc.graphe
 Name

---

 JOB_ID
 START_NODE
 END_NODE
 DISTANCE After an ANALYZE FOR ALL COLUMNS here is what I get:

SQL> break on column_name
SQL> select column_name,ENDPOINT_NUMBER,

   2 ENDPOINT_VALUE , ENDPOINT_ACTUAL_VALUE eav from dba_histograms    3 where table_name='GRAPHE' and owner='SC' order by 1,2,3;

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE EAV

------------------------- --------------- -------------- ---
DISTANCE                               10              0
                                       11             15
                                       12             40
                                       15             50
                                       16             54


[...]

                                       73           8850
                                       74          11853
                                       75          38100
END_NODE                                0             52
                                        1            172
                                        2            321
                                        3            519
                                        4            718
                                        5            887
                                        6           1123
                                        7           1369
                                        8           1725
                                        9           2048
                                       10           2284


[...]

                                       74           4491
                                       75           4554
JOB_ID                                138          11856
                                      282          11926
                                      426          11930
                                      586          14921
                                      729          14924
                                      903          14925
                                     1050          14926
START_NODE                              0            104
                                        1            291
                                        2            431
                                        3            651
                                        4            828
                                        5           1050


[...]

                                       73           4455
                                       74           4488
                                       75           4553

221 rows selected.

And here is what you get with a mere ANALYZE TABLE ESTIMATE STATISTICS:

SQL> select column_name ,ENDPOINT_NUMBER, ENDPOINT_VALUE , EAV

   2 from dba_histograms [...]

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

---------------- --------------- -------------- ---------------------
DISTANCE                       0              0
                               1          38100
END_NODE                       0             52
                               1           4554
JOB_ID                         0          11856
                               1          14926
START_NODE                     0            104
                               1           4553

8 rows selected.

Hence my 2 questions:
1/ The doc. about the 3rd column says "Actual (not normalized) string

   value of the endpoint for this bucket": what does it mean, and when    is it populated ??
2/ Why is DBA_HISTOGRAMS populated so differently whether you use a

   command or the other and how should one interpret the values these    rows contain ?

Thanks a lot.
Sp Received on Mon Jan 31 2005 - 02:53:57 CST