[20200306]hash join會提前終止掃描嗎.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2678783/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180705]關於hash join 2.txt
- Java之 join 等待執行緒終止Java執行緒
- subprocess.Popen 如何提前終止程式執行
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 你可以終止 forEach 嗎？
- [20190815]索引快速全掃描的成本.txt索引
- [20180713]關於hash join 測試中一個疑問.txt
- [20190221]使用nmap掃描埠的問題.txt
- [20200306]expand格式化輸出.txt
- Mysql join 的本質，Nested Loop Join 和 Hash JoinMySqlOOP
- [20210219]全表掃描邏輯讀問題.txt
- 全表掃描和全索引掃描索引
- AWVS掃描器掃描web漏洞操作Web
- 20200909]為什麼執行計劃不是出現hash join semi.txt
- 掃描器的存在、奧普 掃描器
- win10系統掃描器提示掃描不到掃描器如何解決Win10
- 掃描器
- 視覺Mamba的多向掃描策略真的有效嗎？視覺
- 掃描王 for Mac專業圖片掃描工具Mac
- Hash join演算法原理(轉)演算法
- 什麼是漏洞掃描?漏洞掃描功能有哪些?
- MySQL中的全表掃描和索引樹掃描MySql索引
- 目錄掃描
- 埠掃描器
- python掃描埠Python
- DAST 黑盒漏洞掃描器 第四篇：掃描效能AST
- 電腦掃描檔案怎麼掃描 win10電腦掃描檔案方法介紹Win10
- DAST 黑盒漏洞掃描器 第六篇：運營篇(終)AST
- 全表掃描和全索引掃描繼續（PG-TiDB）索引TiDB
- 京東掃描平臺EOS—JS掃描落地與實踐JS
- [20220121]Hash Aggregation.txt
- SELECT COUNT(*) 會造成全表掃描？回去等通知吧
- 掃描行為分析
- 綜合掃描工具
- 淺談掃描線
- P2032 掃描
- Nydus 映象掃描加速
- sonar（二）掃描配置