Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mark.

Asked: July 29, 2004 - 4:56 pm UTC

Last updated: July 29, 2004 - 4:56 pm UTC

Version: 9.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

I'm currently learning how to tune sql by cardinality feedback. I'm using Wolfgang Breitlang's paper 'Using DBMS_STATS in Access Path Opimization' to assist with this tuning experiment.

My question deals with displaying RAW data as readable output. The query listed below is what I have to help with displaying RAW data as readable output. However, I'm having issues with displaying dates properly. I would like to set this up as a function so I could call the function within a sql statement. Can you point me in the right direction on how display RAW data as readable output?

select st.table_name
,st.column_Name
,st.num_distinct ndv
,st.density
,decode(tc.data_type,'NUMBER',to_char(utl_raw.cast_to_number(st.low_value))
,'VARCHAR2',utl_raw.cast_to_varchar2(st.low_value)
,'DATE',to_char(utl_raw.cast_to_number(st.low_value) ),'Hi') lo
, st.high_value hi
,st.num_buckets bkts
from dba_tab_col_statistics st
,dba_tab_columns tc
where st.table_name in ('ADDRESS','PERSON_NAMES')
and st.table_name=tc.table_name
and st.column_name=tc.column_name

Thank you,

Mark

and Tom said...

ops$tkyte@ORA9IR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte@ORA9IR2> drop table t;

Table dropped.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t as select * from all_users;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
2 print_table( '
3 select st.table_name
4 ,st.column_Name
5 ,st.num_distinct ndv
6 ,st.density
7 ,decode(tc.data_type,''NUMBER'',to_char(utl_raw.cast_to_number(st.low_value))
8 ,''VARCHAR2'',utl_raw.cast_to_varchar2(st.low_value)
9 ,''DATE'',
10 (to_number(substr( st.low_value, 1, 2 ), ''xx'' )-100)*100+
11 (to_number(substr( st.low_value, 3, 2 ), ''xx'' )-100)|| ''/'' ||
12 to_number(substr( st.low_value, 5, 2 ), ''xx'' ) || ''/'' ||
13 to_number(substr( st.low_value, 7, 2 ), ''xx'' ) || '' '' ||
14 (to_number(substr( st.low_value, 9, 2 ), ''xx'' )-1) || '':'' ||
15 (to_number(substr( st.low_value,11, 2 ), ''xx'' )-1) || '':'' ||
16 (to_number(substr( st.low_value,13, 2 ), ''xx'' )-1)
17 ,''Hi'') lo
18 , st.high_value hi
19 ,st.num_buckets bkts
20 from user_tab_col_statistics st
21 ,user_tab_columns tc
22 where st.table_name in (''T'')
23 and st.table_name=tc.table_name
24 and st.column_name=tc.column_name
25 ' );
26 end;
27 /
.TABLE_NAME : T
.COLUMN_NAME : USERNAME
.NDV : 43
.DENSITY : .0232558139534884
.LO : ANONYMOUS
.HI : 61
.BKTS : 1
-----------------
.TABLE_NAME : T
.COLUMN_NAME : USER_ID
.NDV : 43
.DENSITY : .0232558139534884
.LO : 0
.HI : C20247
.BKTS : 1
-----------------
.TABLE_NAME : T
.COLUMN_NAME : CREATED
.NDV : 33
.DENSITY : .0303030303030303
.LO : 2002/5/12 16:47:39
.HI : 7868071E10240D
.BKTS : 1
-----------------

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select created, dump(created,16) from (select min(created) created from t);

CREATED
--------------------
DUMP(CREATED,16)
-------------------------------------------------------------------------------
12-may-2002 16:47:39
Typ=12 Len=7: 78,66,5,c,11,30,28



that'll get most reasonable dates (BC, not.... need more math for that)..

</code> http://docs.oracle.com/cd/A57673_01/DOC/api/doc/OCI73/ch3.htm#3-ex-date <code>



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.