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

0    194    1

Tags:

👉 本文共约7066个字,系统预计阅读时间或需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


    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包下的所有函数

    本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
    LOW_VALUE、HIGH_VALUE、ENDPOINT_VALUE的转换--UTL_RAW、HEXSTR、DBMS_STATS.CONVERT_RAW_VALUE的使用后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
    验证码:
    请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

    标签:

    Avatar photo

    小麦苗

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

    您可能还喜欢...

    发表回复

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

    • DB宝
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部