ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120215]显示统计信息的最大最小值.txt

[20120215]显示统计信息的最大最小值.txt

原创 Linux操作系统 作者:lfree 时间:2012-02-15 14:43:34 0 删除 编辑
昨晚,看了一些统计信息的内容,由于里面的信息是RAW datatypes.看了别人写的代码,转换对应的数值:

http://structureddata.org/2007/10/16/how-to-display-high_valuelow_value-columns-from-user_tab_col_statistics/

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
   type=substr(type,1,9)
   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,
   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 = b.owner
   a.table_name='FOO' and
   a.table_name=b.table_name and
   a.column_name=b.column_name
/

http://mwidlake.wordpress.com/2010/02/24/update-to-decoding-high-and-low-values/
-- col_stats
-- Martin Widlake mdw 21/03/2003
-- MDW 11/12/09 enhanced to include more translations of low_value/high_value
-- pilfered from Gary Myers blog
-- MDW 20/02/10 added in the handling of timestamps.
col owner        form. a6 word wrap
col table_name   form. a15 word wrap
col column_name  form. a22 word wrap
col data_type    form. a12
col M            form. a1
col num_vals     form. 99999,999
col dnsty        form. 0.9999
col num_nulls    form. 99999,999
col low_v        form. a30
col low_v2       form. a18
col hi_v         form. a30
col data_type    form. a10
col low_value    form. a25
col high_value   form. a25
set lines 110
break on owner nodup on table_name nodup
spool col_stats.lst
select --owner
--      ,table_name
      column_name
      ,data_type
      ,decode (nullable,'N','Y','N')  M
      ,num_distinct num_vals
      ,num_nulls
      ,density dnsty
,decode(substr(data_type,1,9) -- as there are several timestamp types
  ,'NUMBER'       ,to_char(utl_raw.cast_to_number(low_value))
  ,'VARCHAR2'     ,to_char(utl_raw.cast_to_varchar2(low_value))
  ,'NVARCHAR2'    ,to_char(utl_raw.cast_to_nvarchar2(low_value))
  ,'BINARY_DO',to_char(utl_raw.cast_to_binary_double(low_value))
  ,'BINARY_FL' ,to_char(utl_raw.cast_to_binary_float(low_value))
  ,'DATE',rtrim(
               to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
                      + (to_number(substr(low_value,3,2),'XX')-100),'fm0000')||'-'||
               to_char(to_number(substr(low_value,5,2),'XX'),'fm00')||'-'||
               to_char(to_number(substr(low_value,7,2),'XX'),'fm00')||' '||
               to_char(to_number(substr(low_value,9,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(low_value,11,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(low_value,13,2),'XX')-1,'fm00'))
  ,'TIMESTAMP',rtrim(
               to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
                      + (to_number(substr(low_value,3,2),'XX')-100),'fm0000')||'-'||
               to_char(to_number(substr(low_value,5,2),'XX'),'fm00')||'-'||
               to_char(to_number(substr(low_value,7,2),'XX'),'fm00')||' '||
               to_char(to_number(substr(low_value,9,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(low_value,11,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(low_value,13,2),'XX')-1,'fm00')
              ||'.'||to_number(substr(low_value,15,8),'XXXXXXXX')  )
       ) low_v
,decode(substr(data_type,1,9) -- as there are several timestamp types
  ,'NUMBER'       ,to_char(utl_raw.cast_to_number(high_value))
  ,'VARCHAR2'     ,to_char(utl_raw.cast_to_varchar2(high_value))
  ,'NVARCHAR2'    ,to_char(utl_raw.cast_to_nvarchar2(high_value))
  ,'BINARY_DO',to_char(utl_raw.cast_to_binary_double(high_value))
  ,'BINARY_FL' ,to_char(utl_raw.cast_to_binary_float(high_value))
  ,'DATE',rtrim(
               to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
                      + (to_number(substr(high_value,3,2),'XX')-100),'fm0000')||'-'||
               to_char(to_number(substr(high_value,5,2),'XX'),'fm00')||'-'||
               to_char(to_number(substr(high_value,7,2),'XX'),'fm00')||' '||
               to_char(to_number(substr(high_value,9,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(high_value,11,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(high_value,13,2),'XX')-1,'fm00'))
  ,'TIMESTAMP',rtrim(
               to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
                      + (to_number(substr(high_value,3,2),'XX')-100),'fm0000')||'-'||
               to_char(to_number(substr(high_value,5,2),'XX'),'fm00')||'-'||
               to_char(to_number(substr(high_value,7,2),'XX'),'fm00')||' '||
               to_char(to_number(substr(high_value,9,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(high_value,11,2),'XX')-1,'fm00')||':'||
               to_char(to_number(substr(high_value,13,2),'XX')-1,'fm00')
              ||'.'||to_char(to_number(substr(high_value,15,8),'XXXXXXXX')))
  ,  high_value
       ) hi_v
,low_value,high_value
from dba_tab_columns
where owner      like upper('&tab_own')
and   table_name like upper(nvl('&tab_name','WHOOPS')||'%')
ORDER BY owner,table_name,COLUMN_ID
/
clear colu
spool off
clear breaks


第1个链接不支持许多类型比如timestamp,自己参照第1个例子做了一些修改:
第2个链接还原timestamp最大值时的尾数部分写错,应该是原作者to_char(to_number(substr(high_value,15,8),'XXXXXXXX'))。
utl_raw.cast_to_binary_double(rawval)返回的是负数,前面加一个负号才正确!还有如果double,float为负数的话,转化还存在问题,我不知道现在问题在那里?不过这种类型的数据在实践中使用很少。

create or replace function display_raw (rawval raw, data_type varchar2)
return varchar2
is
   cn     number;
   cv     varchar2(32);
   cd     date;
   ct     timestamp(6);
-- cnv    nvarchar2(32);
   cr     rowid;
   cc     char(32);
   db     BINARY_DOUBLE;
   type1   varchar2(32);
begin
   type1 := substr(data_type,1,9);
   if (type1 = 'NUMBER') then
      dbms_stats.convert_raw_value(rawval, cn);
      return to_char(cn);
   elsif (type1 = 'VARCHAR2') then
      dbms_stats.convert_raw_value(rawval, cv);
      return to_char(cv);
   elsif (type1 = 'DATE') then
      dbms_stats.convert_raw_value(rawval, cd);
       return to_char(cd);
   elsif (type1 = 'TIMESTAMP') then
      dbms_stats.convert_raw_value(rawval, cd);
      return to_char(cd)||'.'||to_char(to_number(substr(rawval,15,8),'XXXXXXXX'));
   elsif (type1 = 'NVARCHAR2') then
      return to_char(utl_raw.cast_to_nvarchar2(rawval));
      --dbms_stats.convert_raw_value(rawval, cnv);
      --return to_char(cnv);
   elsif (type1 = 'ROWID') then
      dbms_stats.convert_raw_value(rawval, cr);
      return to_char(cr);
   elsif (type1 = 'CHAR') then
      dbms_stats.convert_raw_value(rawval, cc);
      return to_char(cc);
   elsif (type1 = 'BINARY_DO') then
      return to_char(-utl_raw.cast_to_binary_double(rawval));
      --dbms_stats.convert_raw_value(rawval, db);
      --return to_char(db);
   elsif (type1 = 'BINARY_FL') then      
      return to_char(-utl_raw.cast_to_binary_float(rawval));
   else
      return 'UNKNOWN DATATYPE';
   end if;
end;
/

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2326
  • 访问量
    6056357