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

0    96    1

👉 本文共约7064个字,系统预计阅读时间或需27分钟。

直方图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

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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;


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等。

点击(此处)折叠或打开

  1. CREATE OR REPLACE FUNCTION FUN_DISPLAY_RAW_LHR(P_RAWVAL RAW,
  2. ​ P_TYPE VARCHAR2)
  3. RETURN VARCHAR2 IS
  4. V_NUMBER NUMBER;
  5. V_VARCHAR2 VARCHAR2(32);
  6. V_DATE DATE;
  7. V_NVARCHAR2 NVARCHAR2(32);
  8. V_ROWID ROWID;
  9. V_CHAR CHAR(32);
  10. BEGIN
  11. IF (P_TYPE = 'NUMBER' OR P_TYPE = 'FLOAT') THEN
  12. DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NUMBER);
  13. RETURN TO_CHAR(V_NUMBER);
  14. ELSIF (P_TYPE = 'VARCHAR2') THEN
  15. DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_VARCHAR2);
  16. RETURN TO_CHAR(V_VARCHAR2);
  17. ELSIF (P_TYPE = 'DATE' OR P_TYPE LIKE 'TIMESTAMP%') THEN
  18. DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_DATE);
  19. RETURN TO_CHAR(V_DATE);
  20. ELSIF (P_TYPE = 'NVARCHAR2') THEN
  21. DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_NVARCHAR2);
  22. RETURN TO_CHAR(V_NVARCHAR2);
  23. ELSIF (P_TYPE = 'ROWID') THEN
  24. DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_ROWID);
  25. RETURN TO_CHAR(V_ROWID);
  26. ELSIF (P_TYPE = 'CHAR') THEN
  27. DBMS_STATS.CONVERT_RAW_VALUE(P_RAWVAL, V_CHAR);
  28. RETURN TO_CHAR(V_CHAR);
  29. ELSIF (P_TYPE = 'RAW') THEN
  30. RETURN TO_CHAR(P_RAWVAL);
  31. ELSE
  32. RETURN 'UNKNOWN DATATYPE!';
  33. END IF;
  34. EXCEPTION
  35. WHEN OTHERS THEN
  36. RETURN 'ERRORS!';
  37. END FUN_DISPLAY_RAW_LHR;

点击(此处)折叠或打开

  1. SELECT D.COLUMN_NAME,
  2. ​ D.LOW_VALUE,
  3. ​ D.HIGH_VALUE,
  4. ​ D.DENSITY,
  5. ​ D.NUM_DISTINCT,
  6. ​ D.NUM_NULLS,
  7. ​ D.NUM_BUCKETS,
  8. ​ D.HISTOGRAM,
  9. ​ D.DATA_TYPE,
  10. ​ FUN_DISPLAY_RAW_LHR(D.LOW_VALUE, D.DATA_TYPE) LOW_VALUE1,
  11. ​ FUN_DISPLAY_RAW_LHR(D.HIGH_VALUE, D.DATA_TYPE) HIGH_VALUE1--,
  12. ​ --UTL_RAW.CAST_TO_NUMBER(D.LOW_VALUE) LOW_VALUE2,
  13. ​ --UTL_RAW.CAST_TO_NUMBER(D.HIGH_VALUE) HIGH_VALUE2,
  14. FROM USER_TAB_COLS D
  15. WHERE D.TABLE_NAME = 'T_AA_20170606_LHR';


ORACLE VERSION 11.2.0.4

下面是utl_raw包下的所有函数

  1. SQL>desc utl_raw
  2. FUNCTION BIT_AND RETURNS RAW
  3. Argument Name Type In/Out Default?

  4. R1 RAW IN
  5. R2 RAW IN
  6. FUNCTION BIT_COMPLEMENT RETURNS RAW
  7. Argument Name Type In/Out Default?

  8. R RAW IN
  9. FUNCTION BIT_OR RETURNS RAW
  10. Argument Name Type In/Out Default?

  11. R1 RAW IN
  12. R2 RAW IN
  13. FUNCTION BIT_XOR RETURNS RAW
  14. Argument Name Type In/Out Default?

  15. R1 RAW IN
  16. R2 RAW IN
  17. FUNCTION CAST_FROM_BINARY_DOUBLE RETURNS RAW
  18. Argument Name Type In/Out Default?

  19. N BINARY_DOUBLE IN
  20. ENDIANESS BINARY_INTEGER IN DEFAULT
  21. FUNCTION CAST_FROM_BINARY_FLOAT RETURNS RAW
  22. Argument Name Type In/Out Default?

  23. N BINARY_FLOAT IN
  24. ENDIANESS BINARY_INTEGER IN DEFAULT
  25. FUNCTION CAST_FROM_BINARY_INTEGER RETURNS RAW
  26. Argument Name Type In/Out Default?

  27. N BINARY_INTEGER IN
  28. ENDIANESS BINARY_INTEGER IN DEFAULT
  29. FUNCTION CAST_FROM_NUMBER RETURNS RAW
  30. Argument Name Type In/Out Default?

  31. N NUMBER IN
  32. FUNCTION CAST_TO_BINARY_DOUBLE RETURNS BINARY_DOUBLE
  33. Argument Name Type In/Out Default?

  34. R RAW IN
  35. ENDIANESS BINARY_INTEGER IN DEFAULT
  36. FUNCTION CAST_TO_BINARY_FLOAT RETURNS BINARY_FLOAT
  37. Argument Name Type In/Out Default?

  38. R RAW IN
  39. ENDIANESS BINARY_INTEGER IN DEFAULT
  40. FUNCTION CAST_TO_BINARY_INTEGER RETURNS BINARY_INTEGER
  41. Argument Name Type In/Out Default?

  42. R RAW IN
  43. ENDIANESS BINARY_INTEGER IN DEFAULT
  44. FUNCTION CAST_TO_NUMBER RETURNS NUMBER
  45. Argument Name Type In/Out Default?

  46. R RAW IN
  47. FUNCTION CAST_TO_NVARCHAR2 RETURNS NVARCHAR2
  48. Argument Name Type In/Out Default?

  49. R RAW IN
  50. FUNCTION CAST_TO_RAW RETURNS RAW
  51. Argument Name Type In/Out Default?

  52. C VARCHAR2 IN
  53. FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2
  54. Argument Name Type In/Out Default?

  55. R RAW IN
  56. FUNCTION COMPARE RETURNS NUMBER
  57. Argument Name Type In/Out Default?

  58. R1 RAW IN
  59. R2 RAW IN
  60. PAD RAW IN DEFAULT
  61. FUNCTION CONCAT RETURNS RAW
  62. Argument Name Type In/Out Default?

  63. R1 RAW IN DEFAULT
  64. R2 RAW IN DEFAULT
  65. R3 RAW IN DEFAULT
  66. R4 RAW IN DEFAULT
  67. R5 RAW IN DEFAULT
  68. R6 RAW IN DEFAULT
  69. R7 RAW IN DEFAULT
  70. R8 RAW IN DEFAULT
  71. R9 RAW IN DEFAULT
  72. R10 RAW IN DEFAULT
  73. R11 RAW IN DEFAULT
  74. R12 RAW IN DEFAULT
  75. FUNCTION CONVERT RETURNS RAW
  76. Argument Name Type In/Out Default?

  77. R RAW IN
  78. TO_CHARSET VARCHAR2 IN
  79. FROM_CHARSET VARCHAR2 IN
  80. FUNCTION COPIES RETURNS RAW
  81. Argument Name Type In/Out Default?

  82. R RAW IN
  83. N NUMBER IN
  84. FUNCTION LENGTH RETURNS NUMBER
  85. Argument Name Type In/Out Default?

  86. R RAW IN
  87. FUNCTION OVERLAY RETURNS RAW
  88. Argument Name Type In/Out Default?

  89. OVERLAY_STR RAW IN
  90. TARGET RAW IN
  91. POS BINARY_INTEGER IN DEFAULT
  92. LEN BINARY_INTEGER IN DEFAULT
  93. PAD RAW IN DEFAULT
  94. FUNCTION REVERSE RETURNS RAW
  95. Argument Name Type In/Out Default?

  96. R RAW IN
  97. FUNCTION SUBSTR RETURNS RAW
  98. Argument Name Type In/Out Default?

  99. R RAW IN
  100. POS BINARY_INTEGER IN
  101. LEN BINARY_INTEGER IN DEFAULT
  102. FUNCTION TRANSLATE RETURNS RAW
  103. Argument Name Type In/Out Default?

  104. R RAW IN
  105. FROM_SET RAW IN
  106. TO_SET RAW IN
  107. FUNCTION TRANSLITERATE RETURNS RAW
  108. Argument Name Type In/Out Default?

  109. R RAW IN
  110. TO_SET RAW IN DEFAULT
  111. FROM_SET RAW IN DEFAULT
  112. PAD RAW IN DEFAULT
  113. FUNCTION XRANGE RETURNS RAW
  114. Argument Name Type In/Out Default?

  115. START_BYTE RAW IN DEFAULT
  116. END_BYTE RAW IN DEFAULT

DBMS_STATS.**CONVERT_RAW_VALUE函数**

  1. SQL> desc dbms_stats
  2. PROCEDURE CONVERT_RAW_VALUE
  3. Argument Name Type In/Out Default?

  4. RAWVAL RAW IN
  5. RESVAL VARCHAR2 OUT
  6. PROCEDURE CONVERT_RAW_VALUE
  7. Argument Name Type In/Out Default?

  8. RAWVAL RAW IN
  9. RESVAL DATE OUT
  10. PROCEDURE CONVERT_RAW_VALUE
  11. Argument Name Type In/Out Default?

  12. RAWVAL RAW IN
  13. RESVAL NUMBER OUT
  14. PROCEDURE CONVERT_RAW_VALUE
  15. Argument Name Type In/Out Default?

  16. RAWVAL RAW IN
  17. RESVAL BINARY_FLOAT OUT
  18. PROCEDURE CONVERT_RAW_VALUE
  19. Argument Name Type In/Out Default?

  20. RAWVAL RAW IN
  21. RESVAL BINARY_DOUBLE OUT


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

下面是简单实验。**

  1. --TABLE存在4个不同类型的字段
  2. SQL> DESC TABLE
  3. Name Null? Type

  4. DIS_NUMBER NOT NULL NUMBER(12)
  5. RSVDC3 VARCHAR2(16)
  6. ORDER_PV NUMBER(12,2)
  7. SALE_DATE DATE
  8. --统计信息收集后,列的统计信息如下。SQL执行计划与索引的使用,会参考统计信息获得的值。
  9. --现在我们主要关注LOW_VALUE,HIGH_VALUE字段的值。
  10. COLUMN_NAME LOW_VALUE HIGH_VALUE

  11. DIS_NUMBER C102 C60A6464646464
  12. RSVDC3 3C6241395166 C40A4D4323
  13. ORDER_PV 3D582C5166 C4400707450B
  14. SALE_DATE 786D0305010101 78C70C04010101

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

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

  3. ​ 1 99999999999
  4. --同样,可以使用utl_raw.CAST_FROM_NUMBER函数转换成数据库的raw格式。
  5. SQL>select utl_raw.CAST_FROM_NUMBER(1) LOW_VALUE,utl_raw.CAST_FROM_NUMBER(99999999999) HIGH_VALUE from dual
  6. ​ LOW_VALUE HIGH_VALUE

  7. ​ C102 C60A6464646464

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

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

  1. DECLARE
    rv RAW(32) := '786D0305010101';
    dt DATE := NULL;
    BEGIN
    dbms_stats.convert_raw_value(rv, dt);
    dbms_output.put_line('LOW_DATE: '||TO_CHAR(dt, 'YYYY-MM-DD hh24:mi:ss'));
    END;
    /
  2. LOW_DATE: 2009-03-05 00:00:00

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

  1. 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);

    BEGIN

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

  2. 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

SQL> declare n varchar2(2000);
2 begin
3 dbms_stats.convert_raw_value('72',n);
4 dbms_output.put_line(n);
5 end;
6 /
r

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

15 + 3 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部