ITPub博客

首页 > 数据库 > Oracle > 20200909]为什么执行计划不是出现hash join semi.txt

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

原创 Oracle 作者:lfree 时间:2020-09-09 21:24:05 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2713
  • 访问量
    6543086