【读书笔记】【收获，不止Oracle】哈希连接表(Hash)

最新推荐文章于 2021-04-07 20:53:00 发布

凡人皆需侍奉

最新推荐文章于 2021-04-07 20:53:00 发布

阅读量576

点赞数

分类专栏：读书笔记 Oracle 文章标签： oracle 读书笔记

本文链接：https://blog.csdn.net/u011877833/article/details/38854331

版权

Oracle 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

读书笔记

8 篇文章 0 订阅

订阅专栏

本次实验用于探究哈希连接表的访问次数。

测试脚本

SELECT /*+leadiing(t1) use_hash(t2) */*
FROM t1,t2
WHERE t1.id=t2.t1_id;

查看执行计划（t2表只会被访问1次或0次）

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9z4ytb42f97nh, child number 0
-------------------------------------
SELECT /*+leadiing(t1) use_hash(t2) */* FROM t1,t2 WHERE t1.id=t2.t1_id

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:01.53 |    1019 |   741K|   741K| 1141K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.03 |       7 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |   TABLE ACCESS FULL| T2   |      1 |    102K|    100K|00:00:00.80 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")

Note
-----
   - dynamic sampling used for this statement

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


23 rows selected.

测试脚本2

select /* + leading(t1) use_hash(t2) */*
from t1,t2
where t1.id=t2.t1_id
and t1.n=999999999;

查看执行计划（t2表被访问0次）

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gs0furjmf9yqy, child number 0
-------------------------------------
select /* + leading(t1) use_hash(t2) */* from t1,t2 where t1.id=t2.t1_id and t1.n=999999999

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |   675K|   675K|  160K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |   TABLE ACCESS FULL| T2   |      0 |    102K|      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."N"=999999999)

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement


24 rows selected.

执行脚本3

select /* + leading(t1) use_hash(t2) */*
from t1,t2
where t1.id=t2.t1_id
and 1=2;

查看执行计划（t1表和t2表都访问0次）

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8a686acupj135, child number 0
-------------------------------------
select /* + leading(t1) use_hash(t2) */* from t1,t2 where t1.id=t2.t1_id and 1=2

Plan hash value: 487071653

-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |      0 |00:00:00.01 |       |       |          |
|*  2 |   HASH JOIN         |      |      0 |    100 |      0 |00:00:00.01 |   732K|   732K|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |    TABLE ACCESS FULL| T1   |      0 |    100 |      0 |00:00:00.01 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      0 |    102K|      0 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)
   2 - access("T1"."ID"="T2"."T1_ID")

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement


25 rows selected.

总结：
在HASH连接中，驱动表和被驱动表都只会访问0次或者1次。