convert low_value and high_value for date datatype in column's index statistics

1. The dictionary view of dba_tab_col_statistics

https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2089.htm#REFRN20275

Note the low_value and high_value are in RAW(32) format.


2. google and found a few article, but only this works correctly for me. As some of  them giving higher number for low_value than high_value. i.e. the low_value is likely to be year 2169, while high value is year 2017. Guess this is computer using two digits to represent years in YY.

http://oracle-study-notes.blogspot.sg/2008/06/how-to-display-highvaluelowvalue.html


sharing his code here. Thanks Mr. Sun.

1. create a function as follows (copied from a website) 

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;  
/ 

2. Issue the following statement with table name and column name 

col column_name format a20
col low_val format a20
col high_val format a20

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,  
   a.num_distinct
from 
   dba_tab_col_statistics a, dba_tab_cols b  
where 
      a.table_name='TABLE_NAME'
  and a.table_name=b.table_name
  and a.column_name=b.column_name  
  and  a.owner='MYOWNER'
  and b.owner='MYOWNER'
--  and a.column_name ='COL_NAME'
/ 

Today I had chance to try this article and found it works well too.

 select low_VALUE , high_value from dba_tab_col_statistics where TABLE_NAME='ACCOUNTS' and COLUMN_NAME='DATETIME';

LOW_VALUE                                                        HIGH_VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
77C50718010101                                                   78710C17103A39

set serveroutput on
DECLARE
rv RAW(32) ;
dt DATE;
BEGIN
select LOW_VALUE into rv from dba_tab_col_statistics where TABLE_NAME='ACCOUNTS' and COLUMN_NAME='DATETIME' and owner='TAN';
dbms_stats.convert_raw_value(rv, dt);
dbms_output.put_line( TO_CHAR(dt, 'dd-MON-yyyy hh:mm'));
END;
/


23-OCT-1970 12:10

PL/SQL procedure successfully completed.



following the code in https://mwidlake.wordpress.com/2010/01/03/decoding-high_value-and-low_value/

The low_value decoded are not correct for date columns.


COLUMN_NAME            DATA_TYPE   LOW_V              HI_V
---------------------- ----------  ------------------ ------------------
ID                     NUMBER      6820               877120576
DATETIME               DATE        2096-7-24 0:0:0    2013-12-23 15:57:56
TRANSACTIONDATE        DATE        2096-7-24 0:0:0    2013-12-23 2:37:3

..
36 rows selected.

..
36 rows selected.