字符串型字段的Frequency histogram解读

本文来源于 http://www.hellodba.com/reader.php?ID=18&lang=EN ， 感谢原作者！

create table t3(c1 varchar2(10) not null);
insert into t3
select to_char(round(dbms_random.value(1,10))) from dual connect by level <= 100;
commit;

exec dbms_stats.gather_table_stats(user, 'T3', method_opt=>'FOR COLUMNS C1 SIZE 254',cascade=>true);
select c1, count(*) from t3
group by c1
order by c1;


with ep as (
  select distinct c1, round(cn, 15 - length(trunc(cn))) ep_value  
        from (select c1,to_number(substrb(utl_raw.cast_to_raw(c1) || '' || lpad('0', 30 - length(utl_raw.cast_to_raw(c1) || ''), '0'), 0,30),   
              lpad('X', 30, 'X')) cn from t3)   
)
select ep.c1 column_actual_value, ep.ep_value column_value, endpoint_number - nvl(prev_endpoint,0) frequency
from (
    select endpoint_number,
        lag(endpoint_number,1) over(order by endpoint_number)  prev_endpoint,
        endpoint_value
    from user_tab_histograms
    where table_name  = 'T3' and column_name = 'C1'
    ) t, ep
    where t.endpoint_value=ep.ep_value
order by endpoint_number;

Oracle取字符串的前32位，然后转换为Raw类型。注意：Oracle会将Raw数字四舍五入到第15位，大大降低精度。