30.读书笔记收获不止Oracle之表的哈希连接
来源:互联网 发布:淘宝洗照片不好 编辑:程序博客网 时间:2024/05/21 19:31
30.读书笔记收获不止Oracle之表的哈希连接
来看下哈希连接的表访问次数
实验表还是和上篇中的一样。
执行如下:
SQL> SELECT /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.id;
SQL>select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8r4tqu5rnv8m0,child number 0
-------------------------------------
SELECT /*+leading(t1) use_hash(t2)*/ * fromt1,t2 where t1.id=t2.id
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | | 100 |00:00:00.01 | 1018 | | | |
|* 1| HASH JOIN | | 1 | 100 | 100 |00:00:00.01 | 1018 | 960K| 960K|1235K (0)|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2| TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3| TABLE ACCESS FULL| T2 | 1 | 118K| 100K|00:00:00.01 | 1011 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- access("T1"."ID"="T2"."ID")
Note
-----
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-dynamic statistics used: dynamic sampling (level=2)
24 rows selected.
T2表职位访问1次。在HASH连接中,驱动表和被驱动表都只会访问0次或者1次。
1. 实验1
SQL> SELECT /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.id and t1.n=999999999;
SQL>select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1m02nqfdvdpqk,child number 0
-------------------------------------
SELECT /*+leading(t1) use_hash(t2)*/ * fromt1,t2 where t1.id=t2.id and
t1.n=999999999
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1| HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | 683K| 683K| 173K (0)|
|* 2| TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
| 3| TABLE ACCESS FULL| T2 | 0 | 118K| 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- access("T1"."ID"="T2"."ID")
2- filter("T1"."N"=999999999)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
26 rows selected.
T1返回0,T2表也是访问0.
2. 实验2
SQL> SELECT /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.id and 1=2;
SQL>select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dv8w4w999knbg,child number 0
-------------------------------------
SELECT /*+leading(t1) use_hash(t2)*/ * fromt1,t2 where t1.id=t2.id and
1=2
Plan hash value: 487071653
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1| FILTER | | 1 | | 0 |00:00:00.01 |
|* 2| HASH JOIN | | 0 | 100 | 0|00:00:00.01 |
| 3| TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 |
| 4| TABLE ACCESS FULL| T2 | 0 | 118K| 0 |00:00:00.01 |
----------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter(NULL IS NOT NULL)
2- access("T1"."ID"="T2"."ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
27 rows selected.
在HASH连接中,驱动表和被驱动表都只会访问0次或者1次。
- 30.读书笔记收获不止Oracle之表的哈希连接
- 【读书笔记】【收获,不止Oracle】哈希连接表(Hash)
- 34.读书笔记收获不止Oracle之表连接场景对比
- 29.读书笔记收获不止Oracle之表的循环嵌套连接
- 31.读书笔记收获不止Oracle之表的合并排序连接
- 33.读书笔记收获不止Oracle之表连接的排序分析
- 《收获,不止Oracle》读书笔记(二):表连接
- 35.读书笔记收获不止Oracle之嵌套循环表连接与索引
- 10.读书笔记收获不止Oracle之 表设计之分区表
- 24.读书笔记收获不止Oracle之 索引的危害
- 【读书笔记】【收获,不止Oracle】不同连接类型表下,驱动顺序对查询性能的影响
- 【读书笔记】【收获,不止Oracle】不同类型下表连接限制的对比
- 5.读书笔记收获不止Oracle之 插入表性能示例
- 9.读书笔记收获不止Oracle之 表设计
- 21.读书笔记收获不止Oracle之 索引回表效率
- 1.读书笔记收获不止Oracle之开篇
- 2.读书笔记收获不止Oracle之 意识
- 13.读书笔记收获不止Oracle之 簇表
- 2017.11.06 hex文件格式分析
- 如何隐藏高德地图的logo(已解决)
- app复杂业务逻辑自动化验证案例分享
- 仿微信底部滑动菜单Tab
- 安卓统一推送联盟成立——何为“推送”?
- 30.读书笔记收获不止Oracle之表的哈希连接
- 31.读书笔记收获不止Oracle之表的合并排序连接
- SpringMVC基础-6-异常处理器
- 打响联通混改第一枪,京东推动第四次零售革命深化
- 32.读书笔记收获不止Oracle之表链接的驱动顺序
- Android 把.java转成.dex
- 移动端接口设计思想
- 33.读书笔记收获不止Oracle之表连接的排序分析
- ng2基本命令