gpt4 book ai didi

sql - sql 语句的哈希值

转载 作者:行者123 更新时间:2023-12-04 14:14:41 27 4
gpt4 key购买 nike

当我们在 Oracle 中执行任何 sql 语句时,都会为该 sql 语句分配一个哈希值并存储到库缓存中。因此,稍后,如果另一个用户请求相同的查询,那么 Oracle 会找到哈希值并执行相同的执行计划。但是,我对哈希值有一个疑问。我的意思是,哈希值是如何生成的? ,我的意思是,Oracle 服务器是否使用某些算法,或者它们只是将 sql 字符串转换为某个数值。

从那以后,我正在阅读 专业版 Oracle SQL 书,上面写着,

select * from employees where department_id = 60;

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;

select /* a_comment */ * from employees where department_id = 60;

会返回不同的hash值,因为在sql语句执行的时候,Oracle先把字符串转换成hash值。但是,当我尝试这样做时,它返回相同的哈希值。
SQL> select * from boats where bid=10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2799518614

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BOATS | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | B_PK | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("BID"=10)

SQL> SELECT * FROM BOATS WHERE BID=10;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2799518614

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BOATS | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | B_PK | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("BID"=10)

最佳答案

在您的问题文本中,您似乎在描述 sql_id和/或 hash_value .这是 SQL 语句文本的散列值,Oracle 使用它来确定共享池中是否已存在特定 SQL 语句。但是,您在示例中显示的是 plan_hash_value这是为 SQL 语句生成的计划的哈希值。两者之间可能存在多对多关系。单个 SQL 语句 ( sql_id/hash_value ) 可以有多个不同的计划 ( plan_hash_value ) 并且多个不同的 SQL 语句可以共享相同的计划。

因此,例如,如果我编写了两个不同的 SQL 语句来查询 EMP 中的特定行。表,我会得到相同的 plan_hash_value .

SQL> set autotrace traceonly;
SQL> select * from emp where ename = 'BOB';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 39 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='BOB')


SQL> ed
Wrote file afiedt.buf

1* select * FROM emp WHERE ename = 'BOB'
SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 39 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='BOB')

如果我查看 v$sql ,但是,我会看到两个不同的 sql_idhash_value值已生成
SQL> set autotrace off;
SQL> ed
Wrote file afiedt.buf

1 select sql_id, sql_text, hash_value, plan_hash_value
2 from v$sql
3 where sql_text like 'select%BOB%'
4* and length(sql_text) < 50
SQL> /

SQL_ID SQL_TEXT HASH_VALUE PLAN_HASH_VALUE
------------- ---------------------------------------- ---------- ---------------
161v96c0v9c0n select * FROM emp WHERE ename = 'BOB' 28618772 3956160932
cvs1krtgzfr78 select * from emp where ename = 'BOB' 1610046696 3956160932

Oracle 认识到这两个语句是不同的查询,具有不同的 sql_idhash_value哈希值。但是它们碰巧生成了相同的计划,因此它们最终得到相同的 plan_hash_value .

关于sql - sql 语句的哈希值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16011976/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com