ITPub博客

首页 > 数据库 > Oracle > 将列统计信息中的high_value low_value 转换成可以读懂的数字,字符和日期

将列统计信息中的high_value low_value 转换成可以读懂的数字,字符和日期

原创 Oracle 作者:bestpaydata 时间:2015-11-13 16:50:15 0 删除 编辑

high_value low_value可以用dbms_stats.convert_raw_value转换成可以读懂的数字,字符和日期

select column_name ,high_value,low_value,histogram from user_tab_columns where table_name='T1' and column_name in ('ID','CDATE');
COLUMN_NAME          HIGH_VALUE           LOW_VALUE            HISTOGRAM
-------------------- -------------------- -------------------- ---------------
ID                   C302                 C102                 NONE
CDATE                323031372D31322D3136 323031352D30332D3233 NONE
                     2032313A33363A3030   2030303A30303A3030

create or replace function display_raw (rawval raw, type varchar2)
  return varchar2
  is
     cn     number;
     cv     varchar2(32);
     cd     date;
     cnv    nvarchar2(32);
     cr     rowid;
     cc     char(32);
 begin
    if (type = 'NUMBER') then
       dbms_stats.convert_raw_value(rawval, cn);
       return to_char(cn);
    elsif (type = 'VARCHAR2') then
       dbms_stats.convert_raw_value(rawval, cv);
       return to_char(cv);
    elsif (type = 'DATE') then
       dbms_stats.convert_raw_value(rawval, cd);
       return to_char(cd);
    elsif (type = 'NVARCHAR2') then
       dbms_stats.convert_raw_value(rawval, cnv);
       return to_char(cnv);
    elsif (type = 'ROWID') then
       dbms_stats.convert_raw_value(rawval, cr);
       return to_char(cnv);
    elsif (type = 'CHAR') then
       dbms_stats.convert_raw_value(rawval, cc);
       return to_char(cc);
    else
       return 'UNKNOWN DATATYPE';
    end if;
 end;
 /



 
 select
    a.column_name,
    a.num_distinct,
    display_raw(a.low_value,b.data_type) as low_val,
    display_raw(a.high_value,b.data_type) as high_val,
    b.data_type
 from
    dba_tab_col_statistics a, dba_tab_cols b
 where
   a.owner='SYS' and
   a.table_name='T1' and
   a.table_name=b.table_name and
   a.column_name=b.column_name and
   a.column_name = 'CDATE'   --ID
   and b.owner=a.owner
order by 1, 2;

COLUMN_NAME          NUM_DISTINCT LOW_VAL              HIGH_VAL             DATA_TYPE
-------------------- ------------ -------------------- -------------------- --------------------
CDATE                       10000 2015-03-23 00:00:00  2017-12-16 21:36:00  VARCHAR2


COLUMN_NAME          NUM_DISTINCT LOW_VAL              HIGH_VAL             DATA_TYPE
-------------------- ------------ -------------------- -------------------- --------------------
ID                          10000 1                    10000                NUMBER





XLJ

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

上一篇: 直方图 二
下一篇: PHP面向对象编程
请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985638