LOW_VALUE、HIGH_VALUE、ENDPOINT_VALUE转换--UTL_RAW、DBMS_STATS.CONVERT_RAW_VALUE LOW_VALUE、HIGH_VALUE、ENDPOINT_VALUE的转换--UTL_RAW、HEXSTR、DBMS_STATS.CONVERT_RAW_VALUE的使用

直方图ENDPOINT_VALUE转换：

首先准备基础表：

CREATE TABLE T_ST_20170605_LHR(ID NUMBER,STR VARCHAR2(30));

INSERT INTO T_ST_20170605_LHR SELECT ROWNUM ID,1 STR FROM DUAL CONNECT BY LEVEL<=10001;

UPDATE T_ST_20170605_LHR T SET T.STR=6 WHERE T.ID=10001;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ST_20170605_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS STR SIZE 2');

查看直方图信息：

LHR@orclasm > COL COLUMN_NAME FORMAT A15

LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME = 'T_ST_20170605_LHR';

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM

--------------- ------------ ---------- ----------- ---------------

STR                        2          0           2 FREQUENCY

LHR@orclasm > SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_VALUE,ENDPOINT_NUMBER FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ST_20170605_LHR';

TABLE_NAME                     COLUMN_NAME     ENDPOINT_VALUE ENDPOINT_NUMBER

------------------------------ --------------- -------------- ---------------

T_ST_20170605_LHR              STR                 2.5442E+35           10000

T_ST_20170605_LHR              STR                 2.8038E+35           10001

这里的ENDPOINT_VALUE值需要去转换，字符‘1’的16进制的dump值为0x31，字符‘6’的16进制的dump值为0x36，

LHR@orclasm > SELECT DUMP('1',16),DUMP('6',16) FROM DUAL; 

DUMP('1',16)     DUMP('6',16)

---------------- ----------------

Typ=96 Len=1: 31 Typ=96 Len=1: 36

将0x31右边补0一直补到15个字节（共30位），再将其转换为10进制数，0x36类似，如下所示：

LHR@orclasm > SELECT TO_NUMBER('310000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') C1,TO_NUMBER('360000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') C2 FROM DUAL; 

        C1         C2

---------- ----------

2.5442E+35 2.8038E+35

可以看到转换后的结果和之前查询出来的结果一致。为了方便转换给出如下函数：

CREATE OR REPLACE FUNCTION HEXSTR(P_NUMBER IN NUMBER) RETURN VARCHAR2 AS

  L_STR    LONG := TO_CHAR(P_NUMBER, 'fm' || RPAD('x', 50, 'x'));

  L_RETURN VARCHAR2(4000);

BEGIN

  WHILE (L_STR IS NOT NULL) LOOP

    L_RETURN := L_RETURN || CHR(TO_NUMBER(SUBSTR(L_STR, 1, 2), 'xx'));

    L_STR    := SUBSTR(L_STR, 3);

  END LOOP;

  RETURN(SUBSTR(L_RETURN, 1, 6));

END;

再次查询：

LHR@orclasm > COL ENDPOINT_VALUE2 FORMAT A15

LHR@orclasm > SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_VALUE,ENDPOINT_NUMBER,HEXSTR(ENDPOINT_VALUE) ENDPOINT_VALUE2 FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ST_20170605_LHR';

TABLE_NAME                     COLUMN_NAME     ENDPOINT_VALUE ENDPOINT_NUMBER ENDPOINT_VALUE2

------------------------------ --------------- -------------- --------------- ---------------

T_ST_20170605_LHR              STR                 2.5442E+35           10000 1

T_ST_20170605_LHR              STR                 2.8038E+35           10001 6

列统计信息LOW_VALUE、HIGH_VALUE的转换：

使用DBMS_STATS.CONVERT_RAW_VALUE或UTL_RAW.CAST_TO_NUMBER、UTL_RAW.CAST_TO_VARCHAR2等。

点击(此处)折叠或打开

CREATE OR REPLACE FUNCTION FUN_DISPLAY_RAW_LHR(P_RAWVAL RAW,

                                               P_TYPE VARCHAR2)

  RETURN VARCHAR2 IS

  V_NUMBER NUMBER;

  V_VARCHAR2 VARCHAR2(32);

  V_DATE DATE;

  V_NVARCHAR2 NVARCHAR2(32);

  V_ROWID ROWID;

  V_CHAR CHAR(32);

  IF (P_TYPE = 'NUMBER' OR P_TYPE = 'FLOAT') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NUMBER);

    RETURN TO_CHAR(V_NUMBER);

  ELSIF (P_TYPE = 'VARCHAR2') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_VARCHAR2);

    RETURN TO_CHAR(V_VARCHAR2);

  ELSIF (P_TYPE = 'DATE' OR P_TYPE LIKE 'TIMESTAMP%') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_DATE);

    RETURN TO_CHAR(V_DATE);

  ELSIF (P_TYPE = 'NVARCHAR2') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NVARCHAR2);

    RETURN TO_CHAR(V_NVARCHAR2);

  ELSIF (P_TYPE = 'ROWID') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_ROWID);

    RETURN TO_CHAR(V_ROWID);

  ELSIF (P_TYPE = 'CHAR') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_CHAR);

    RETURN TO_CHAR(V_CHAR);

  ELSIF (P_TYPE = 'RAW') THEN

    RETURN TO_CHAR(P_RAWVAL);

  ELSE

    RETURN 'UNKNOWN DATATYPE!';

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    RETURN 'ERRORS!';

END FUN_DISPLAY_RAW_LHR;

SELECT D.COLUMN_NAME,

       D.LOW_VALUE,

       D.HIGH_VALUE,

       D.DENSITY,

       D.NUM_DISTINCT,

       D.NUM_NULLS,

       D.NUM_BUCKETS,

       D.HISTOGRAM,

       D.DATA_TYPE,

       FUN_DISPLAY_RAW_LHR(D.LOW_VALUE, D.DATA_TYPE) LOW_VALUE1,

       FUN_DISPLAY_RAW_LHR(D.HIGH_VALUE, D.DATA_TYPE) HIGH_VALUE1--,

       --UTL_RAW.CAST_TO_NUMBER(D.LOW_VALUE) LOW_VALUE2,

       --UTL_RAW.CAST_TO_NUMBER(D.HIGH_VALUE) HIGH_VALUE2,

  FROM USER_TAB_COLS D

 WHERE D.TABLE_NAME = 'T_AA_20170606_LHR';

ORACLE VERSION 11.2.0.4

下面是utl_raw包下的所有函数

SQL>desc utl_raw

FUNCTION BIT_AND RETURNS RAW

 Argument Name Type In/Out Default?

 ------------------------------ ----------------------- ------ --------

 R1 RAW IN

 R2 RAW IN

FUNCTION BIT_COMPLEMENT RETURNS RAW

 R RAW IN

FUNCTION BIT_OR RETURNS RAW

FUNCTION BIT_XOR RETURNS RAW

FUNCTION CAST_FROM_BINARY_DOUBLE RETURNS RAW

 N BINARY_DOUBLE IN

 ENDIANESS BINARY_INTEGER IN DEFAULT

FUNCTION CAST_FROM_BINARY_FLOAT RETURNS RAW

 N BINARY_FLOAT IN

FUNCTION CAST_FROM_BINARY_INTEGER RETURNS RAW

 N BINARY_INTEGER IN

FUNCTION CAST_FROM_NUMBER RETURNS RAW

 N NUMBER IN

FUNCTION CAST_TO_BINARY_DOUBLE RETURNS BINARY_DOUBLE

FUNCTION CAST_TO_BINARY_FLOAT RETURNS BINARY_FLOAT

FUNCTION CAST_TO_BINARY_INTEGER RETURNS BINARY_INTEGER

FUNCTION CAST_TO_NUMBER RETURNS NUMBER

FUNCTION CAST_TO_NVARCHAR2 RETURNS NVARCHAR2

FUNCTION CAST_TO_RAW RETURNS RAW

 C VARCHAR2 IN

FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2

FUNCTION COMPARE RETURNS NUMBER

 PAD RAW IN DEFAULT

FUNCTION CONCAT RETURNS RAW

 R1 RAW IN DEFAULT

 R2 RAW IN DEFAULT

 R3 RAW IN DEFAULT

 R4 RAW IN DEFAULT

 R5 RAW IN DEFAULT

 R6 RAW IN DEFAULT

 R7 RAW IN DEFAULT

 R8 RAW IN DEFAULT

 R9 RAW IN DEFAULT

 R10 RAW IN DEFAULT

 R11 RAW IN DEFAULT

 R12 RAW IN DEFAULT

FUNCTION CONVERT RETURNS RAW

 TO_CHARSET VARCHAR2 IN

 FROM_CHARSET VARCHAR2 IN

FUNCTION COPIES RETURNS RAW

FUNCTION LENGTH RETURNS NUMBER

FUNCTION OVERLAY RETURNS RAW

 OVERLAY_STR RAW IN

 TARGET RAW IN

 POS BINARY_INTEGER IN DEFAULT

 LEN BINARY_INTEGER IN DEFAULT

FUNCTION REVERSE RETURNS RAW

FUNCTION SUBSTR RETURNS RAW

 POS BINARY_INTEGER IN

FUNCTION TRANSLATE RETURNS RAW

 FROM_SET RAW IN

 TO_SET RAW IN

FUNCTION TRANSLITERATE RETURNS RAW

 TO_SET RAW IN DEFAULT

 FROM_SET RAW IN DEFAULT

FUNCTION XRANGE RETURNS RAW

 START_BYTE RAW IN DEFAULT

 END_BYTE RAW IN DEFAULT

DBMS_STATS.CONVERT_RAW_VALUE函数

SQL> desc dbms_stats

PROCEDURE CONVERT_RAW_VALUE

 RAWVAL RAW IN

 RESVAL VARCHAR2 OUT

 RESVAL DATE OUT

 RESVAL NUMBER OUT

 RESVAL BINARY_FLOAT OUT

 RESVAL BINARY_DOUBLE OUT

utl_raw、CONVERT_RAW_VALUE使用在 字符数值比对、统计信息等指标数值转换上。 

下面是简单实验。

--TABLE存在4个不同类型的字段

SQL> DESC TABLE

 Name                      Null?     Type

 ------------------------- -------- ----------------------------

 DIS_NUMBER                NOT NULL NUMBER(12)

 RSVDC3                             VARCHAR2(16)

 ORDER_PV                           NUMBER(12,2)

 SALE_DATE                          DATE

--统计信息收集后，列的统计信息如下。SQL执行计划与索引的使用，会参考统计信息获得的值。

--现在我们主要关注LOW_VALUE,HIGH_VALUE字段的值。

COLUMN_NAME    LOW_VALUE          HIGH_VALUE 

-------------- ------------------ ------------------------ 

DIS_NUMBER     C102               C60A6464646464 

RSVDC3         3C6241395166       C40A4D4323

ORDER_PV       3D582C5166         C4400707450B

SALE_DATE      786D0305010101     78C70C04010101

使用utl_raw.CAST_TO_NUMBER函数获取DIS_NUMBER字段，LOW_VALUE与HIGH_VALUE的值。

SQL> select utl_raw.CAST_TO_NUMBER('C102') low_num,utl_raw.CAST_TO_NUMBER('C60A6464646464') high_num from dual

   LOW_NUM HIGH_NUM

---------- ----------------------

         1 99999999999

--同样，可以使用utl_raw.CAST_FROM_NUMBER函数转换成数据库的raw格式。

SQL>select utl_raw.CAST_FROM_NUMBER(1) LOW_VALUE,utl_raw.CAST_FROM_NUMBER(99999999999) HIGH_VALUE from dual

         LOW_VALUE  HIGH_VALUE

------------------ ------------------------

             C102   C60A6464646464

同理，VARCHAR类型的值，也可使用utl_raw.CAST_TO_VARCHAR2 

DATE类型使用dbms_stats.convert_raw_value()函数。

DECLARE

 rv RAW(32) := '786D0305010101';

 dt DATE := NULL;

  dbms_stats.convert_raw_value(rv, dt);

  dbms_output.put_line('LOW_DATE: '||TO_CHAR(dt, 'YYYY-MM-DD hh24:mi:ss'));

/

LOW_DATE: 2009-03-05 00:00:00

下面自定义函数，利用dbms_stats.convert_raw_value函数，可快速获得不同类型的真实值。

                                               P_TYPE   VARCHAR2)

  RETURN VARCHAR2 IS

  V_NUMBER    NUMBER;

  V_VARCHAR2  VARCHAR2(32);

  V_DATE      DATE;

  V_NVARCHAR2 NVARCHAR2(32);

  V_ROWID     ROWID;

  V_CHAR      CHAR(32);

  IF (P_TYPE = 'NUMBER' OR P_TYPE = 'FLOAT') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NUMBER);

    RETURN TO_CHAR(V_NUMBER);

  ELSIF (P_TYPE = 'VARCHAR2') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_VARCHAR2);

    RETURN TO_CHAR(V_VARCHAR2);

  ELSIF (P_TYPE = 'DATE' OR P_TYPE LIKE 'TIMESTAMP%') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_DATE);

    RETURN TO_CHAR(V_DATE);

  ELSIF (P_TYPE = 'NVARCHAR2') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NVARCHAR2);

    RETURN TO_CHAR(V_NVARCHAR2);

  ELSIF (P_TYPE = 'ROWID') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_ROWID);

    RETURN TO_CHAR(V_ROWID);

  ELSIF (P_TYPE = 'CHAR') THEN

    DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_CHAR);

    RETURN TO_CHAR(V_CHAR);

  ELSIF (P_TYPE = 'RAW') THEN

    RETURN TO_CHAR(P_RAWVAL);

  ELSE

    RETURN 'UNKNOWN DATATYPE!';

  END IF;

  WHEN OTHERS THEN

    RETURN 'ERRORS!';

SELECT D.TABLE_NAME, D.COLUMN_NAME, 

       D.LOW_VALUE,

       D.HIGH_VALUE,

       D.DENSITY,

       D.NUM_DISTINCT,

       D.NUM_NULLS,

       D.NUM_BUCKETS,

       D.HISTOGRAM,

       D.DATA_TYPE,

       FUN_DISPLAY_RAW_LHR(D.LOW_VALUE, D.DATA_TYPE) LOW_VALUE1,

       FUN_DISPLAY_RAW_LHR(D.HIGH_VALUE, D.DATA_TYPE) LOW_VALUE2

  FROM USER_TAB_COLS D

 WHERE D.LOW_VALUE IS NOT NULL 

 AND D.TABLE_NAME='T_ROWS_20170605_LHR'

 AND ROWNUM<=10;

COLUMN_NAME                    PARTITION_NAME     NUM_DISTINCT LOW_VALUE          HIGH_VALUE               DATA_TYPE

------------------------------ ------------------ ------------ ------------------ ------------------------ ----------------

ALLOCATION_DIST_NUMBER         P_MOH_2012_10              4964 6737               9999520                  NUMBER

ALLOCATION_DIST_NUMBER         P_MOH_2012_11              4989 6737               9999956                  NUMBER

ALLOCATION_DIST_NUMBER         P_MOH_2012_12              5351 6737               9999936                  NUMBER

ALLOCATION_DIST_NUMBER         P_MOH_2012_6                 12 7007633            9961247                  NUMBER

ALLOCATION_DIST_NUMBER         P_MOH_2012_7                380 7000028            9996912                  NUMBER

CURRENCY_CODE                  P_MOH_2014_4                  3 HKD                RMB                      VARCHAR2

CURRENCY_CODE                  P_MOH_2014_5                  3 HKD                RMB                      VARCHAR2

CURRENCY_CODE                  P_MOH_2014_6                  3 HKD                RMB                      VARCHAR2

CURRENCY_CODE                  P_MOH_2014_7                  3 HKD                RMB                      VARCHAR2

INVOICE_SALES_DATE             P_MOH_2014_10                78 06-SEP-14          23-JAN-15                DATE

INVOICE_SALES_DATE             P_MOH_2014_11                77 14-OCT-14          26-JAN-15                DATE

INVOICE_SALES_DATE             P_MOH_2014_12                75 10-NOV-14          01-FEB-15                DATE

INVOICE_SALES_DATE             P_MOH_2014_2                 40 12-FEB-14          29-MAR-14                DATE

INVOICE_SALES_DATE             P_MOH_2014_3                 81 01-MAR-14          30-APR-14                DATE

还原DUMP出来的数字

SQL> select dump(2000,16) from dual;

DUMP(2000,16)

------------------

Typ=2 Len=2: c2,15

SQL> declare n number;

  2  begin

  3   dbms_stats.convert_raw_value('c215',n);

  4  dbms_output.put_line(n);

  5  end;

  6  /

2000

declare n number;

     begin

     dbms_stats.convert_raw_value('c215',n);

     dbms_output.put_line(n);

     end;

   /

还原DUMP的字符串

SQL> select dump('robinson',16) from dual;

DUMP('ROBINSON',16)

-------------------------------------

Typ=96 Len=8: 72,6f,62,69,6e,73,6f,6e

SQL> declare n varchar2(2000);

  2       begin

  3       dbms_stats.convert_raw_value('726f62696e736f6e',n);

  4       dbms_output.put_line(n);

  5       end;

  6     /

robinson

SQL> select dump('robinson') from dual;

DUMP('ROBINSON')

--------------------------------------------

Typ=96 Len=8: 114,111,98,105,110,115,111,110

其实这个时候DUMP出来的是以10进制显示的，也就是说114相对于72，也就是r,后面的字幕同样

SQL> select to_char(114,'xxx') from dual;

TO_C

----

  72

  3       dbms_stats.convert_raw_value('72',n);

r

About Me ............................................................................................................................... ● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用 ● QQ群：230161599     微信群：私聊 ● 联系我请加QQ好友(646634621)，注明添加缘由 ● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。

<a target="_blank" href="http://wpa.qq.com/msgrd?v=3&amp;uin=646634621&amp;site=qq&amp;menu=yes"></a>