oracle 查看列统计信息中 low_value

最新推荐文章于 2023-12-15 08:43:15 发布

朝闻道-夕死可矣

最新推荐文章于 2023-12-15 08:43:15 发布

阅读量1.2k

点赞数

分类专栏： ORACLE

本文链接：https://blog.csdn.net/jc_benben/article/details/51658626

版权

ORACLE 专栏收录该内容

79 篇文章 0 订阅

订阅专栏

-- 准备环境  
create table t1(id int,name varchar2(10));  
begin  
  for i in 1001..100000 loop  
    insert into  t1 values(i,'china'||to_char(i));  
  end loop;  
  commit;  
end; 

-- 收集信息
begin
  dbms_stats.gather_table_stats(ownname => 'LOGE',tabname => 'SMALL',cascade => true);
end;

-- 创建函数
create or replace function f_display_raw(rawval raw, type varchar2)
  return varchar2 is
  cn  number;
  cv  varchar2(32);
  cd  date;
  cnv nvarchar2(32);
  cr  rowid;
  cc  char(32);
  cbf binary_float;
  cbd binary_double;
begin
  if (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 = 'NUMBER') then
    dbms_stats.convert_raw_value(rawval, cn);
    return to_char(cn);
  elsif (type = 'BINARY_FLOAT') then
    dbms_stats.convert_raw_value(rawval, cbf);
    return to_char(cbf);
  elsif (type = 'BINARY_DOUBLE') then
    dbms_stats.convert_raw_value(rawval, cbd);
    return to_char(cbd);
  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(cr);
  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 user_tab_col_statistics a, user_tab_cols b
 where a.table_name = 'T1'
   and a.table_name = b.table_name
   and a.column_name = b.column_name;