20200909]为什么执行计划不是出现hash join semi.txt

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

[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. --//平时自己也很少注意这些细节问题...

编辑推荐：

返回列表

20200909]为什么执行计划不是出现hash join semi.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推