合 LOW_VALUE、HIGH_VALUE、ENDPOINT_VALUE的转换--UTL_RAW、HEXSTR、DBMS_STATS.CONVERT_RAW_VALUE的使用
Tags: Oracle
直方图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;
1 | 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);
- 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.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包下的所有函数