字符串型字段的Frequency histogram解读

来源：互联网发布：淘宝海蓝之谜小样真假编辑：程序博客网时间：2024/06/11 14:29

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

create table t3(c1 varchar2(10) not null);insert into t3select 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 t3group by c1order 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) frequencyfrom (    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_valueorder by endpoint_number;

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

0 0