Structured Data: Oracle Database Performance And Scalability Blog

How to Display HIGH_VALUE/LOW_VALUE Columns from USER_TAB_COL_STATISTICS

Here is some code to display the HIGH_VALUE/LOW_VALUE columns from USER_TAB_COL_STATISTICS which are stored as RAW datatypes.

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,
   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='FOO' and
   a.table_name=b.table_name and
   a.column_name=b.column_name
/

COLUMN_NAME          LOW_VAL          HIGH_VAL         DATA_TYPE
-------------------- ---------------- ---------------- ---------
ORGANIZATION_ID      00D000000000062  00D300000000tgk  CHAR
UG_ID                00500000000008U  00GD0000000mBda  CHAR
USERS_ID             005000000000063  00G30000000mBcq  CHAR
IS_TRANSITIVE        0                1                CHAR
SUPPRESS_RULES       0                1                CHAR