[20200306]hash join会提前终止扫描吗.txt

来源：这里教程网时间：2026-03-03 15:15:04 作者：

[20200306]hash join会提前终止扫描吗.txt --//链接http://www.itpub.net/thread-2128301-1-1.html问的问题。 select /*+ leading(a) use_hash(a b) */ * from a,b where a.id=b.id and rownum<50;假设a，b采用hash连接，a是驱动表，那么是先扫描a.id生成hash表，然后再依次扫描b.id进行匹配，如果没扫完b.id就有50条符合条件的数据了，会提前终止扫描吗？ select * from (select /*+ leading(a) use_hash(a b) */ * from a,b where a.id=b.id order by b.name ) where rownum<50; 假设b.name上有索引，那么可以根据b.name进行index_full_scan扫描b.id来规避排序吗？ --//测试看看。 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table a (id1 number,id2 number,vc varchar2(100)); insert into a select rownum,rownum,lpad('a',100,'a') from dual connect by level<=1e6; create table b (id1 number,id2 number,vc varchar2(100)); insert into b select rownum,rownum,lpad('a',100,'a') from dual connect by level<=1e6; commit ; --//分析略。 2.测试： SCOTT@book> alter session set statistics_level = all; Session altered. SCOTT@book> select /*+ leading(a) use_hash(a b) */ * from a,b where a.id1=b.id1 and rownum<5; ID1 ID2 VC ID1 ID2 VC ---- ---- -------------- ----- ----- ------------------------ 255 255 aaaaaaaaaaaaaa 255 255 aaaaaaaaaaaaaaaaaaaaaaaaa 256 256 aaaaaaaaaaaaaa 256 256 aaaaaaaaaaaaaaaaaaaaaaaaa 257 257 aaaaaaaaaaaaaa 257 257 aaaaaaaaaaaaaaaaaaaaaaaaa 258 258 aaaaaaaaaaaaaa 258 258 aaaaaaaaaaaaaaaaaaaaaaaaa SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 4bpuvuaq1qpaq, child number 1 ------------------------------------- select /*+ leading(a) use_hash(a b) */ * from a,b where a.id1=b.id1 and rownum<5 Plan hash value: 2594705369 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | | 20442 (100)| | 4 |00:00:00.98 | 31393 | 16217 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | | 4 |00:00:00.98 | 31393 | 16217 | | | | |* 2 | HASH JOIN | | 1 | 4 | 888 | 117M| 20442 (1)| 00:04:06 | 4 |00:00:00.98 | 31393 | 16217 | 150M| 8913K| 184M (0)| | 3 | TABLE ACCESS FULL| A | 1 | 1000K| 105M| | 4402 (1)| 00:00:53 | 1000K|00:00:00.16 | 31389 | 16217 | | | | | 4 | TABLE ACCESS FULL| B | 1 | 1000K| 105M| | 4402 (1)| 00:00:53 | 4 |00:00:00.01 | 4 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / A@SEL$1 4 - SEL$1 / B@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<5) 2 - access("A"."ID1"="B"."ID1") --//A作为驱动表，全表扫描走hash是不可避免，但是b表仅仅输出取出4行并且匹配就ok了。 SCOTT@book> select * from b where rownum<=5; ID1 ID2 VC ---- ---- ---------------------------------------------------------------------------------------------------- 255 255 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 256 256 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 257 257 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 258 258 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 259 259 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 3.继续测试： SCOTT@book> update b set vc=id2||substr(vc,1,6); 1000000 rows updated. SCOTT@book> commit ; Commit complete. SCOTT@book> create index i_b_vc on b(vc); Index created. --//分析略。 SCOTT@book> select * from ( select /*+ leading(a) use_hash(a b) index_asc(b i_b_vc) */ a.id2 idxa,b.id2 idxg,b.vc vcb from a,b where a.id1=b.id1 order by b.vc) where rownum<5; Plan hash value: 3339728813 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 20410 (100)| | 4 |00:00:01.21 | 47459 | 16217 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | | 4 |00:00:01.21 | 47459 | 16217 | | | | | 2 | VIEW | | 1 | 1000K| 74M| | 20410 (1)| 00:04:05 | 4 |00:00:01.21 | 47459 | 16217 | | | | |* 3 | SORT ORDER BY STOPKEY| | 1 | 1000K| 31M| 42M| 20410 (1)| 00:04:05 | 4 |00:00:01.21 | 47459 | 16217 | 2048 | 2048 | 2048 (0)| |* 4 | HASH JOIN | | 1 | 1000K| 31M| 20M| 11508 (1)| 00:02:19 | 1000K|00:00:01.08 | 47459 | 16217 | 50M| 9345K| 49M (0)| | 5 | TABLE ACCESS FULL | A | 1 | 1000K| 9765K| | 4402 (1)| 00:00:53 | 1000K|00:00:00.15 | 31232 | 16217 | | | | | 6 | TABLE ACCESS FULL | B | 1 | 1000K| 21M| | 4402 (1)| 00:00:53 | 1000K|00:00:00.15 | 16227 | 0 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//不行 SCOTT@book> alter table b modify vc not null ; Table altered. SCOTT@book> select * from ( select /*+ leading(a) use_hash(a b) index_asc(b i_b_vc) */ a.id2 idxa,b.id2 idxg,b.vc vcb from a,b where a.id1=b.id1 order by b.vc) where rownum<5; Plan hash value: 1704849001 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 221K(100)| | 4 |00:00:01.70 | 236K| 16217 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | | 4 |00:00:01.70 | 236K| 16217 | | | | | 2 | VIEW | | 1 | 1000K| 74M| | 221K (1)| 00:44:13 | 4 |00:00:01.70 | 236K| 16217 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 1000K| 31M| 42M| 221K (1)| 00:44:13 | 4 |00:00:01.70 | 236K| 16217 | 2048 | 2048 | 2048 (0)| |* 4 | HASH JOIN | | 1 | 1000K| 31M| 20M| 212K (1)| 00:42:26 | 1000K|00:00:01.56 | 236K| 16217 | 50M| 9345K| 49M (0)| | 5 | TABLE ACCESS FULL | A | 1 | 1000K| 9765K| | 4402 (1)| 00:00:53 | 1000K|00:00:00.16 | 31232 | 16217 | | | | | 6 | TABLE ACCESS BY INDEX ROWID| B | 1 | 1000K| 21M| | 204K (1)| 00:41:00 | 1000K|00:00:00.60 | 204K| 0 | | | | | 7 | INDEX FULL SCAN | I_B_VC | 1 | 1000K| | | 3342 (1)| 00:00:41 | 1000K|00:00:00.17 | 3336 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//选择索引，但是 INDEX FULL SCAN。没有达到预期效果。 SCOTT@book> select /*+ leading(a) use_hash(a b) index_asc(b i_b_vc) */ a.id2 idxa,b.id2 idxg,b.vc vcb from a,b where a.id1=b.id1 and rownum<5 order by b.vc ; Plan hash value: 1523373104 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 212K(100)| | 4 |00:00:00.56 | 31237 | 16217 | | | | | 1 | SORT ORDER BY | | 1 | 4 | 132 | | 212K (1)| 00:42:26 | 4 |00:00:00.56 | 31237 | 16217 | 2048 | 2048 | 2048 (0)| |* 2 | COUNT STOPKEY | | 1 | | | | | | 4 |00:00:00.56 | 31237 | 16217 | | | | |* 3 | HASH JOIN | | 1 | 4 | 132 | 20M| 212K (1)| 00:42:26 | 4 |00:00:00.56 | 31237 | 16217 | 50M| 9345K| 49M (0)| | 4 | TABLE ACCESS FULL | A | 1 | 1000K| 9765K| | 4402 (1)| 00:00:53 | 1000K|00:00:00.16 | 31232 | 16217 | | | | | 5 | TABLE ACCESS BY INDEX ROWID| B | 1 | 1000K| 21M| | 204K (1)| 00:41:00 | 4 |00:00:00.01 | 5 | 0 | | | | | 6 | INDEX FULL SCAN | I_B_VC | 1 | 1000K| | | 3342 (1)| 00:00:41 | 4 |00:00:00.01 | 3 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//走的是INDEX FULL SCAN ，如果提示控制注，结果一定是对的这样写。注意vc一定有约束not null。 SCOTT@book> alter table b modify vc null ; Table altered. SCOTT@book> select /*+ leading(a) use_hash(a b) index_asc(b i_b_vc) */ a.id2 idxa,b.id2 idxg,b.vc vcb from a,b where a.id1=b.id1 and rownum<5 order by b.vc ; IDXA IDXG VCB ---------- ---------- --------- 255 255 255aaaaaa 256 256 256aaaaaa 257 257 257aaaaaa 258 258 258aaaaaa Plan hash value: 3352745223 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 11509 (100)| | 4 |00:00:00.55 | 31235 | 16217 | | | | | 1 | SORT ORDER BY | | 1 | 4 | 132 | | 11509 (1)| 00:02:19 | 4 |00:00:00.55 | 31235 | 16217 | 2048 | 2048 | 2048 (0)| |* 2 | COUNT STOPKEY | | 1 | | | | | | 4 |00:00:00.55 | 31235 | 16217 | | | | |* 3 | HASH JOIN | | 1 | 4 | 132 | 20M| 11508 (1)| 00:02:19 | 4 |00:00:00.55 | 31235 | 16217 | 50M| 9345K| 49M (0)| | 4 | TABLE ACCESS FULL| A | 1 | 1000K| 9765K| | 4402 (1)| 00:00:53 | 1000K|00:00:00.16 | 31232 | 16217 | | | | | 5 | TABLE ACCESS FULL| B | 1 | 1000K| 21M| | 4402 (1)| 00:00:53 | 4 |00:00:00.01 | 3 | 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//结果不对。 --//不知道像这样优化有什么更好的方法。 SCOTT@book> alter table b modify vc null ; Table altered. WITH bx AS ( SELECT /*+ index_asc(b i_b_vc) */ * FROM b ORDER BY vc) select * from (SELECT /*+ leading(a) use_hash(a bx) */ a.id2 idxa, bx.id2 idxg, bx.vc vcb FROM a, bx WHERE a.id1 = bx.id1 ) where ROWNUM < 5; Plan hash value: 3611937128 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 214K(100)| | 4 |00:00:00.50 | 16226 | 16217 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | | 4 |00:00:00.50 | 16226 | 16217 | | | | |* 2 | HASH JOIN | | 1 | 4 | 320 | 20M| 214K (1)| 00:42:52 | 4 |00:00:00.50 | 16226 | 16217 | 50M| 9345K| 49M (0)| | 3 | TABLE ACCESS FULL | A | 1 | 1000K| 9765K| | 4402 (1)| 00:00:53 | 1000K|00:00:00.12 | 16220 | 16217 | | | | | 4 | VIEW | | 1 | 1000K| 66M| | 204K (1)| 00:41:00 | 4 |00:00:00.01 | 6 | 0 | | | | | 5 | TABLE ACCESS BY INDEX ROWID| B | 1 | 1000K| 21M| | 204K (1)| 00:41:00 | 4 |00:00:00.01 | 6 | 0 | | | | | 6 | INDEX FULL SCAN | I_B_VC | 1 | 1000K| | | 3342 (1)| 00:00:41 | 4 |00:00:00.01 | 4 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

编辑推荐：

返回列表

[20200306]hash join会提前终止扫描吗.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推