20200909]為什麼執行計劃不是出現hash join semi.txt
[20200909]為什麼執行計劃不是出現hash join semi.txt
--//別人問的問題,為什麼執行計劃hash join semi,透過例子說明問題:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select * from emp where exists (select 1 from dept where dname='SALES' and dept.deptno=emp.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
6 rows selected.
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8053b6kh802ky, child number 0
-------------------------------------
select * from emp where exists (select 1 from dept where dname='SALES'
and dept.deptno=emp.deptno)
Plan hash value: 367693176
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 6 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 255 | 5 (0)| 00:00:01 | 6 |00:00:00.01 | 10 | 2545K| 2545K| 785K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | I_DEPT_DNAME | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 4 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 8 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / DEPT@SEL$2
3 - SEL$5DA710D3 / DEPT@SEL$2
4 - SEL$5DA710D3 / EMP@SEL$1
--//別人問為什麼不出現HASH JOIN semi.而是hash join,是連線順序的問題嗎?因為這樣dept是驅動表.
select /*+ leading(emp) */ * from emp where exists (select 1 from dept where dname='SALES' and dept.deptno=emp.deptno);
Plan hash value: 4254668763
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 6 |00:00:00.01 | 9 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 255 | 5 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 1098K| 1098K| 664K (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_DEPT_DNAME | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//一樣沒有出現HASH JOIN SEMI連線.因為dept.deptno是主鍵嗎?
3.繼續:
SCOTT@test01p> create table deptx as select * from dept ;
Table created.
select * from emp where exists (select 1 from deptx dept where dname='SALES' and dept.deptno=emp.deptno);
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5scp0hawkubrw, child number 0
-------------------------------------
select * from emp where exists (select 1 from deptx dept where
dname='SALES' and dept.deptno=emp.deptno)
Plan hash value: 1460795715
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 6 |00:00:00.01 | 17 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 5 | 255 | 6 (0)| 00:00:01 | 6 |00:00:00.01 | 17 | 1098K| 1098K| 669K (0)|
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL| DEPTX | 1 | 1 | 13 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 10 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
--//這次出現HASH JOIN SEMI.
SCOTT@test01p> create index i_deptx_dname on deptx(dname);
Index created.
select * from emp where exists (select 1 from deptx dept where dname='SALES' and dept.deptno=emp.deptno);
Plan hash value: 2867647663
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 6 |00:00:00.01 | 9 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 5 | 255 | 5 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 1098K| 1098K| 691K (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPTX | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_DEPTX_DNAME | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//這次也出現HASH JOIN SEMI.如果將dept作為驅動表呢?
select /*+ leading(dept@sub ) */ * from emp where exists (select /*+ qb_name(sub) */ 1 from deptx dept where dname='SALES' and dept.deptno=emp.deptno);
Plan hash value: 743985058
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 6 |00:00:00.01 | 46 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 255 | 6 (17)| 00:00:01 | 6 |00:00:00.01 | 46 | 2545K| 2545K| 675K (0)|
| 2 | SORT UNIQUE | | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPTX | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_DEPTX_DNAME | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以發現這種情況,只要子查詢展開後,dept作為驅動表,Operation就不會出現HASH JOIN SEMI.
--//前面沒有出現hash join semi主要原因是dept.deptno是主鍵或者唯一索引.
SCOTT@test01p> create unique index i_deptx_deptno on deptx(deptno);
Index created.
select * from emp where exists (select 1 from deptx dept where dname='SALES' and dept.deptno=emp.deptno);
Plan hash value: 2939079003
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 6 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 255 | 5 (0)| 00:00:01 | 6 |00:00:00.01 | 10 | 2545K| 2545K| 710K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPTX | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | I_DEPTX_DNAME | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 4 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 8 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//這樣就不出現HASH JOIN SEMI.
--//平時自己也很少注意這些細節問題...
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2718394/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 從Hash Join的執行計劃的細節中能看到點啥
- [20230921]為什麼執行計劃不再awr中.txt
- 執行緒join為什麼在解構函式中執行緒函式
- [20221104]執行計劃一樣Plan hash value不同.txt
- [20210119]看執行計劃可以使用hash_value.txt
- 檢視執行計劃出現ORA-22992錯誤
- 執行計劃-1：獲取執行計劃
- 【專案管理經驗分享】為什麼專案計劃難以完美執行？專案管理
- 為什麼執行緒安全的List推薦使用CopyOnWriteArrayList，而不是Vector執行緒
- join、volatile、newSingleThreadLatch 實現執行緒順序執行thread執行緒
- 資料庫實現原理#4（Hash Join）資料庫
- Redis作為單執行緒 為什麼我用它還是出現了超賣呢？Redis執行緒
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- Mysql join 的本質，Nested Loop Join 和 Hash JoinMySqlOOP
- win10出現ls不是內部或外部命令也不是可執行怎麼恢復Win10
- MySQL執行計劃MySql
- SYBASE執行計劃
- MySQL 執行計劃MySql
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- python為什麼要用執行緒Python執行緒
- 為什麼？為什麼StringBuilder是執行緒不安全的？UI執行緒
- MySQL執行計劃解析MySql
- mysql explain 執行計劃MySqlAI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- 執行計劃執行步驟原則
- Promise為什麼比setTimeout先執行？Promise
- 為什麼Podman執行容器更安全？
- Redis不是一直號稱單執行緒效率也很高嗎，為什麼又採用多執行緒了？Redis執行緒
- 執行計劃沒變，執行時快時慢是怎麼回事？
- 來自靈魂的拷問——知道什麼是SQL執行計劃嗎？SQL
- 為什麼redis是單執行緒的以及為什麼這麼快？Redis執行緒
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- Java中多執行緒啟動，為什麼呼叫的是start方法，而不是run方法？Java執行緒
- 執行緒池管理(1)-為什麼需要執行緒池執行緒
- redis為什麼用單執行緒不用多執行緒Redis執行緒