Oracle Performance tuning - Histogram

Histogram的最大最小值，是oracle internal的东西，用sql select查询出来的值是经过加密的。

比如: all_tab_cols.low_value 与 all_tab_cols.high_value，存储了某个表的某一个字段的最大最小值，但是直接select出来是密码文，不可读。

oracle提供了两种方法来解决这个读取最大最小值的问题：

1.  utl_raw 包

2.  dbms_stats 包

以下是utl_raw包的应用，dbms_stats必须以pl/sql来调用，所以暂时没做研究：

select *
from
(select owner,table_name,column_name,data_type,
       low_value,
       decode( DATA_TYPE,
              'CLOB',UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE),
              'BLOB',UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE),
              'NUMBER',UTL_RAW.CAST_TO_NUMBER(LOW_VALUE),
              'CHAR',UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE),
              'VARCHAR2',UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE),
              'DATE',TO_DATE('01-MAY-11','DD-MON-YYYY'),
              UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE)) AS LOW_VALUE_TEXT,
      high_value,
      decode( DATA_TYPE,
              'CLOB',UTL_RAW.CAST_TO_VARCHAR2(high_value),
              'BLOB',UTL_RAW.CAST_TO_VARCHAR2(high_value),
              'NUMBER',UTL_RAW.CAST_TO_NUMBER(high_value),
              'CHAR',UTL_RAW.CAST_TO_VARCHAR2(high_value),
              'VARCHAR2',UTL_RAW.CAST_TO_VARCHAR2(high_value),
              'DATE',TO_DATE('01-JUN-11','DD-MON-YYYY'),
              UTL_RAW.CAST_TO_VARCHAR2(high_value)) AS high_VALUE_TEXT
from all_tab_cols
where owner='SC' and ( table_name='ACTIVITYM1' or table_name='PROBSUMMARYA2' or table_name='PROBSUMMARYM1' )
)

实例中的Owner,table_name都是自己开发库上的，不是必须