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;

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
3. FUNCTION BIT_AND RETURNS RAW
4. Argument Name Type In/Out Default?

5. ---

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

11. ---

12. R RAW IN
14. FUNCTION BIT_OR RETURNS RAW
15. Argument Name Type In/Out Default?

16. ---

17. R1 RAW IN
18. R2 RAW IN
20. FUNCTION BIT_XOR RETURNS RAW
21. Argument Name Type In/Out Default?

22. ---

23. R1 RAW IN
24. R2 RAW IN
26. FUNCTION CAST_FROM_BINARY_DOUBLE RETURNS RAW
27. Argument Name Type In/Out Default?

28. ---

29. N BINARY_DOUBLE IN
30. ENDIANESS BINARY_INTEGER IN DEFAULT
32. FUNCTION CAST_FROM_BINARY_FLOAT RETURNS RAW
33. Argument Name Type In/Out Default?

34. ---

35. N BINARY_FLOAT IN
36. ENDIANESS BINARY_INTEGER IN DEFAULT
38. FUNCTION CAST_FROM_BINARY_INTEGER RETURNS RAW
39. Argument Name Type In/Out Default?

40. ---

41. N BINARY_INTEGER IN
42. ENDIANESS BINARY_INTEGER IN DEFAULT
44. FUNCTION CAST_FROM_NUMBER RETURNS RAW
45. Argument Name Type In/Out Default?

46. ---

47. N NUMBER IN
49. FUNCTION CAST_TO_BINARY_DOUBLE RETURNS BINARY_DOUBLE
50. Argument Name Type In/Out Default?

51. ---

52. R RAW IN
53. ENDIANESS BINARY_INTEGER IN DEFAULT
55. FUNCTION CAST_TO_BINARY_FLOAT RETURNS BINARY_FLOAT
56. Argument Name Type In/Out Default?

57. ---

58. R RAW IN
59. ENDIANESS BINARY_INTEGER IN DEFAULT
61. FUNCTION CAST_TO_BINARY_INTEGER RETURNS BINARY_INTEGER
62. Argument Name Type In/Out Default?

63. ---

64. R RAW IN
65. ENDIANESS BINARY_INTEGER IN DEFAULT
67. FUNCTION CAST_TO_NUMBER RETURNS NUMBER
68. Argument Name Type In/Out Default?

69. ---

70. R RAW IN
71. FUNCTION CAST_TO_NVARCHAR2 RETURNS NVARCHAR2
72. Argument Name Type In/Out Default?

73. ---

74. R RAW IN
76. FUNCTION CAST_TO_RAW RETURNS RAW
77. Argument Name Type In/Out Default?

78. ---

79. C VARCHAR2 IN
81. FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2
82. Argument Name Type In/Out Default?

83. ---

84. R RAW IN
85. FUNCTION COMPARE RETURNS NUMBER
86. Argument Name Type In/Out Default?

87. ---

88. R1 RAW IN
89. R2 RAW IN
90. PAD RAW IN DEFAULT
92. FUNCTION CONCAT RETURNS RAW
93. Argument Name Type In/Out Default?

94. ---

95. R1 RAW IN DEFAULT
96. R2 RAW IN DEFAULT
97. R3 RAW IN DEFAULT
98. R4 RAW IN DEFAULT
99. R5 RAW IN DEFAULT
100. R6 RAW IN DEFAULT
101. R7 RAW IN DEFAULT
102. R8 RAW IN DEFAULT
103. R9 RAW IN DEFAULT
104. R10 RAW IN DEFAULT
105. R11 RAW IN DEFAULT
106. R12 RAW IN DEFAULT
108. FUNCTION CONVERT RETURNS RAW
109. Argument Name Type In/Out Default?

110. ---

111. R RAW IN
112. TO_CHARSET VARCHAR2 IN
113. FROM_CHARSET VARCHAR2 IN
115. FUNCTION COPIES RETURNS RAW
116. Argument Name Type In/Out Default?

117. ---

118. R RAW IN
119. N NUMBER IN
120. FUNCTION LENGTH RETURNS NUMBER
121. Argument Name Type In/Out Default?

122. ---

123. R RAW IN
125. FUNCTION OVERLAY RETURNS RAW
126. Argument Name Type In/Out Default?

127. ---

128. OVERLAY_STR RAW IN
129. TARGET RAW IN
130. POS BINARY_INTEGER IN DEFAULT
131. LEN BINARY_INTEGER IN DEFAULT
132. PAD RAW IN DEFAULT
133. FUNCTION REVERSE RETURNS RAW
134. Argument Name Type In/Out Default?

135. ---

136. R RAW IN
138. FUNCTION SUBSTR RETURNS RAW
139. Argument Name Type In/Out Default?

140. ---

141. R RAW IN
142. POS BINARY_INTEGER IN
143. LEN BINARY_INTEGER IN DEFAULT
144. FUNCTION TRANSLATE RETURNS RAW
145. Argument Name Type In/Out Default?

146. ---

147. R RAW IN
148. FROM_SET RAW IN
149. TO_SET RAW IN
151. FUNCTION TRANSLITERATE RETURNS RAW
152. Argument Name Type In/Out Default?

153. ---

154. R RAW IN
155. TO_SET RAW IN DEFAULT
156. FROM_SET RAW IN DEFAULT
157. PAD RAW IN DEFAULT
159. FUNCTION XRANGE RETURNS RAW
160. Argument Name Type In/Out Default?

161. ---

162. START_BYTE RAW IN DEFAULT
163. 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. ---

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

10. ---

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

16. ---

17. RAWVAL RAW IN
19. RESVAL NUMBER OUT
20. PROCEDURE CONVERT_RAW_VALUE
21. Argument Name Type In/Out Default?

22. ---

23. RAWVAL RAW IN
24. RESVAL BINARY_FLOAT OUT
26. PROCEDURE CONVERT_RAW_VALUE
27. Argument Name Type In/Out Default?

28. ---

29. RAWVAL RAW IN
30. RESVAL BINARY_DOUBLE OUT

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

下面是简单实验。**

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

4. ---

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

13. ---

14. DIS_NUMBER C102 C60A6464646464
15. RSVDC3 3C6241395166 C40A4D4323
16. ORDER_PV 3D582C5166 C4400707450B
17. 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
3. LOW_NUM HIGH_NUM

4. ---

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

12. ---

13. ​ 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,

   本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
   后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

   验证码：

   请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

   相关文章

   • Oracle如何锁住或解锁统计信息？
   • 为什么有的Oracle的执行计划不在AWR中
   • 在鲲鹏arm服务器上安装Oracle 19c
   • Oracle之补全日志（Supplemental Logging）、附加日志
   • Oracle 12c中的统一审计
   • Oracle告警日志里记录了“KILL SOFT -/-/-”会话被杀掉的信息
   • Oracle迁移到PG的几款工具
   • Oracle 19c升级数据库补丁遇到的一些问题
   • 在Oracle中，对表执行TRUNCATE操作会将表的统计信息也清除掉吗
   • Oracle如何查询表的DML操作数据变化量
   • Oracle中的基数（Cardinality）和可选择率（Selectivity）
   • 在Oracle中，当收集表的统计信息时应该注意哪些问题
   • Oracle如何查询表和索引的历史统计信息
   • Oracle基表COL_USAGE$的作用是什么？
   • Oracle中的统计信息之直方图（Histogram）系列
   • 在Oracle中，什么是待定的统计信息（Pending Statistic）
   • 什么是Oracle的基数反馈（Cardinality Feedback）？
   • Oracle统计信息之动态采样（Dynamic Sampling）
   • Oracle中的ora.oc4j、ora.qosmserver服务
   • 在Oracle中，什么是orachk工具
   打赏赞(1)分享