Wednesday, January 10, 2007

High and Low values from *_TAB_COLUMNS

The various _TAB_COLUMNS views (user, all, dba) contain the high and low values of the column picked up when stats were last gathered on the columns.
The drawback is that they are in RAW format.
Even worse, SQL Developer doesn't display the contents of RAWs yet (but please add your voice to the feature request)
This query will translate the high and low values of the common datatypes into something more readable.

select column_name, data_type, data_length, nullable, num_distinct,sample_size,
case when data_type IN ('CHAR', 'VARCHAR2') then utl_raw.cast_to_varchar2(low_value)
when data_type = 'NUMBER' then to_char(utl_raw.cast_to_number(low_value))
when data_type = 'DATE' then
to_char(1780+to_number(substr(low_value,1,2),'XX')+to_number(substr(low_value,3,2),'XX'))||'.'||
to_number(substr(low_value,5,2),'XX')||'.'||
to_number(substr(low_value,7,2),'XX')||'.'||
(to_number(substr(low_value,9,2),'XX')-1)||'.'||
(to_number(substr(low_value,11,2),'XX')-1)||'.'||
(to_number(substr(low_value,13,2),'XX')-1)
end trans_low,
case when data_type IN ('CHAR', 'VARCHAR2') then utl_raw.cast_to_varchar2(high_value)
when data_type = 'NUMBER' then to_char(utl_raw.cast_to_number(high_value))
when data_type = 'DATE' then
to_char(1780+to_number(substr(high_value,1,2),'XX')+to_number(substr(high_value,3,2),'XX'))||'.'||
to_number(substr(high_value,5,2),'XX')||'.'||
to_number(substr(high_value,7,2),'XX')||'.'||
(to_number(substr(high_value,9,2),'XX')-1)||'.'||
(to_number(substr(high_value,11,2),'XX')-1)||'.'||
(to_number(substr(high_value,13,2),'XX')-1)
end trans_high, num_nulls, num_buckets
from dba_tab_columns
where table_name = '...'
and owner = '...'
and column_name in ('...','...')
order by column_id
/

If someone can do something for timestamps, please add a comment.

After a comment on Martin Widlake's blog from Maxim, the logic for the DATE datatype should be amended to
rtrim(
  ltrim(to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
    + (to_number(substr(high_value,3,2),'XX')-100),'0000'))||'.'||
  ltrim(to_char(to_number(substr(high_value,5,2),'XX'),'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,7,2),'XX'),'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,9,2),'XX')-1,'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,11,2),'XX')-1,'00'))||'.'||
  ltrim(to_char(to_number(substr(high_value,13,2),'XX')-1,'00')))


You can also build some wrapper functions around the procedures in DBMS_STATS (assuming you are allowed to create objects in the database). Thanks for the link Herald

create or replace function stats_raw_to_date (p_in raw) return varchar2 is
  v_date date;
  v_char varchar2(25);
begin
  dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
  v_char := to_char(v_date,'YYYY.MM.DD.HH24.MI.SS');
  return v_char;
exception
  when others then return null;
end;
/

1 comment:

Herald ten Dam said...

Hi,

Jonathan Lewis wrote about some time ago about the same values (see http://jonathanlewis.wordpress.com/2006/11/29/low_value-high_value/)
It's possible to see the value of hig h and low value of timestamp to use the date function. So you can see the date-value of a timestamp.