Interpreting histogram values

Querying the user_histograms view you might get null values in endpoint_actual_value column. You can work around this issue and get the value by converting endpoint_value. In the case of a varchar field, you can use hexstr function as Tom Kyte suggests. In the case of a date field, you can get the value with to_date function, specifying the format mask 'J' (Julian):

select table_name table
, column_name column
, endpoint_number number
, endpoint_value value
, endpoint_actual_value actual_value
, to_date (endpoint_value, 'J') date_value
from user_histograms
where table_name = :table_name
and column_name = :column_name
order by 1, 2, 3;

TABLE COLUMN NUMBER VALUE ACTUAL_VALUE DATE_VALUE
------------------------------------------------------------
TABLE COLUMN 0 2454774 3.11.2008
TABLE COLUMN 1 2454775 4.11.2008
TABLE COLUMN 7 2454778 7.11.2008
TABLE COLUMN 8 2454783 12.11.2008
TABLE COLUMN 9 2454790 19.11.2008
TABLE COLUMN 10 2454792 21.11.2008
TABLE COLUMN 11 2454797 26.11.2008
TABLE COLUMN 12 2454799 28.11.2008
TABLE COLUMN 13 2454802 1.12.2008
TABLE COLUMN 24 2454804 3.12.2008
TABLE COLUMN 26 2454806 5.12.2008
TABLE COLUMN 27 2454813 12.12.2008
TABLE COLUMN 28 2454816 15.12.2008
TABLE COLUMN 29 2454817 16.12.2008
TABLE COLUMN 30 2454820 19.12.2008
TABLE COLUMN 31 2454823 22.12.2008
TABLE COLUMN 32 2454825 24.12.2008
TABLE COLUMN 35 2454830 29.12.2008
TABLE COLUMN 36 2454831 30.12.2008
TABLE COLUMN 37 2454837 5.1.2009
TABLE COLUMN 38 2454859 27.1.2009
TABLE COLUMN 68 2455002 19.6.2009

You can obtain a "graphical" representation of the histogram by using analytic function LAG to get the difference of endpoint values and padding a string accordingly:

select to_date (endpoint_value, 'J') date_endpoint_value
, rpad('X', endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number), 'X') bucket_size
from user_histograms
where table_name = :table_name
and column_name = :column_name
order by date_endpoint_value;

ENDPOINT_VALUE WIDTH
------------------------------------------------
03.11.2008 X
04.11.2008 X
07.11.2008 XXXXXX
12.11.2008 X
19.11.2008 X
21.11.2008 X
26.11.2008 X
28.11.2008 X
01.12.2008 X
03.12.2008 XXXXXXXXXXX
05.12.2008 XX
12.12.2008 X
15.12.2008 X
16.12.2008 X
19.12.2008 X
22.12.2008 X
24.12.2008 X
29.12.2008 XXX
30.12.2008 X
05.01.2009 X
27.01.2009 X
19.06.2009 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

For some fancy graphics you can use Oracle APEX. Based on the previous query you can generate graphs like this one:



You can easily build an APEX application for graphic representation of all your histograms.