column的high_value low_value

可以通过 XX_TAB_COL ,TAB_COL_STATISTICS查询字段的low_value,high_value。该字段为RAW类型。
    
计算方式为在最小值基础上取DUMP,转换为16进制，取前32位数据，然后将逗号替换为0。
参考脚本如下：

点击(此处)折叠或打开

1. select trim(upper(replace(substr(dump(min(C_ID), 16, 0, 32),
   
2.                                    instr(dump(min(C_ID), 16, 0, 32), ':', 1) + 1),
   
3.                             ',',
   
4.                             '0'))) low_value,
   
5.          trim(upper(replace(substr(dump(max(c_id), 16, 0, 32),
   
6.                                    instr(dump(max(C_ID), 16, 0, 32), ':', 1) + 1),
   
7.                             ',',
   
8.                             '0'))) high_value
   
9.     from temp_0830;

用如下自定义函数可以查看low_value，high_value的原始值

点击(此处)折叠或打开

1. create or replace function display_raw (rawval raw, type varchar2)
   
2. return varchar2
   
3. is
   
4.    cn number;
   
5.    cv varchar2(32);
   
6.    cd date;
   
7.    cnv nvarchar2(32);
   
8.    cr rowid;
   
9.    cc char(32);
   
10. begin
    
11.    if (type = 'NUMBER') then
    
12.       dbms_stats.convert_raw_value(rawval, cn);
    
13.       return to_char(cn);
    
14.    elsif (type = 'VARCHAR2') then
    
15.       dbms_stats.convert_raw_value(rawval, cv);
    
16.       return to_char(cv);
    
17.    elsif (type = 'DATE') then
    
18.       dbms_stats.convert_raw_value(rawval, cd);
    
19.       return to_char(cd);
    
20.    elsif (type = 'NVARCHAR2') then
    
21.       dbms_stats.convert_raw_value(rawval, cnv);
    
22.       return to_char(cnv);
    
23.    elsif (type = 'ROWID') then
    
24.       dbms_stats.convert_raw_value(rawval, cr);
    
25.       return to_char(cnv);
    
26.    elsif (type = 'CHAR') then
    
27.       dbms_stats.convert_raw_value(rawval, cc);
    
28.       return to_char(cc);
    
29.    else
    
30.       return 'UNKNOWN DATATYPE';
    
31.    end if;
    
32. end;
    
33. 
    
34. select
    
35.    a.column_name,
    
36.    display_raw(a.low_value,b.data_type) as low_val,
    
37.    display_raw(a.high_value,b.data_type) as high_val,
    
38.    b.data_type
    
39. from
    
40.    user_tab_col_statistics a, user_tab_cols b
    
41. where
    
42.    a.table_name='table_name' and
    
43.    a.table_name=b.table_name and
    
44.    a.column_name=b.column_name

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28963243/viewspace-2124182/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28963243/viewspace-2124182/