[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 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2678783/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
