温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

数据库中如何验证SQL ID与SQL HASH VALUE转换

发布时间:2021-11-10 14:47:11 来源:亿速云 阅读:160 作者:小新 栏目:关系型数据库

这篇文章主要介绍了数据库中如何验证SQL ID与SQL HASH VALUE转换,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。

实验目的:验证SQL ID与SQL HASH VALUE转换

SQL> set linesize 1000

SQL> select count(1) from tab_01;

  COUNT(1)

----------

     85128

SQL> select * from table(dbms_xplan.display_cursor( null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID  6mhbq2s7z9ydn, child number 0

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

select count(1) from tab_01

Plan hash value: 4218700832

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

| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |        |       |   342 (100)|          |

|   1 |  SORT AGGREGATE    |        |     1 |            |          |

PLAN_TABLE_OUTPUT

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

|   2 |   TABLE ACCESS FULL| TAB_01 | 85128 |   342   (1)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1

   2 - SEL$1 / TAB_01@SEL$1

Outline Data

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

PLAN_TABLE_OUTPUT

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      DB_VERSION('11.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      FULL(@"SEL$1" "TAB_01"@"SEL$1")

      END_OUTLINE_DATA

  */

PLAN_TABLE_OUTPUT

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

Column Projection Information (identified by operation id):

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

   1 - (#keys=0) COUNT(*)[22]

已选择39行。

SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select count(1) from tab_01%';

SQL_TEXT

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

SQL_ID                     HASH_VALUE CHILD_NUMBER

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

select count(1) from tab_01

6mhbq2s7z9ydn               267712948            0

SQL> select lower(trim('6mhbq2s7z9ydn')) sql_id,trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('6mhbq2s7z9ydn ')), level, 1)) - 1) *

  2       power(32, length(trim('6mhbq2s7z9ydn ')) - level)), power(2, 32))) hash_value  from dual  connect by level <= length(trim('6mhbq2s7z9ydn'));

SQL_ID                     HASH_VALUE

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

6mhbq2s7z9ydn               267712948

感谢你能够认真阅读完这篇文章,希望小编分享的“数据库中如何验证SQL ID与SQL HASH VALUE转换”这篇文章对大家有帮助,同时也希望大家多多支持亿速云,关注亿速云行业资讯频道,更多相关知识等着你来学习!

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI