alter session set statistics_level=all;
select /*+ use_hash(emp,dept) */ ename,job,sal ,dname,loc from emp,dept where emp.deptno=dept.deptno;
SQL> select /*+ use_hash(emp,dept) */ ename,job,sal ,dname,loc from emp,dept where emp.deptno=dept.deptno;
ENAME JOB SAL DNAME LOC
------------------------------ --------------------------- ---------- ------------------------------------------ ---------------------------------------
SMITH CLERK 800 RESEARCH DALLAS
ALLEN SALESMAN 1600 SALES CHICAGO
WARD SALESMAN 1250 SALES CHICAGO
JONES MANAGER 2975 RESEARCH DALLAS
MARTIN SALESMAN 1250 SALES CHICAGO
BLAKE MANAGER 2850 SALES CHICAGO
CLARK MANAGER 2450 ACCOUNTING NEW YORK
SCOTT ANALYST 3000 RESEARCH DALLAS
KING PRESIDENT 5000 ACCOUNTING NEW YORK
TURNER SALESMAN 1500 SALES CHICAGO
ADAMS CLERK 1100 RESEARCH DALLAS
JAMES CLERK 950 SALES CHICAGO
FORD ANALYST 3000 RESEARCH DALLAS
MILLER CLERK 1300 ACCOUNTING NEW YORK
已选择14行。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gq39gq5cu6jk9, child number 0
-------------------------------------
select /*+ use_hash(emp,dept) */ ename,job,sal ,dname,loc from emp,dept
where emp.deptno=dept.deptno
Plan hash value: 615168685
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 13 | 1321K| 1321K| 1094K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
已选择21行。
DEPT离HASH JOIN关键字最近，表示DEPT是驱动表。 Starts等于1，表示两个表都只扫描了一次。
再次强调NESTED LOOPS被驱动表会扫描多次。注意观察Omem,1Mem,Used-Mem，它表示HASH JOIN 会消耗PGA，
当驱动表太大，PGA不能容纳驱动表时，就会产生on-disk HASH JOIN。
现在再回去看看NESTED LOOPS，它没有Omem,1Mem,Used-Mem，也就是说NESTED LOOPS不消耗PGA。
我们再看执行计划中 ID=1 这步，它有*号，前面提到，执行计划中有*表示这个操作有过滤(filter)或者是有access。
HASH JOIN属于access。通过谓词过滤信息，我们可以知道HASH JOIN的JOIN列是哪些列在做JOIN。
这里就是emp.deptno 和dept.deptno做JOIN。
select /*+ use_hash(emp,dept) */ ename,job,sal ,dname,loc from emp,dept where emp.deptno<>dept.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dvf1p4vhyvh37, child number 0
-------------------------------------
select /*+ use_hash(emp,dept) */ ename,job,sal ,dname,loc from emp,dept
where emp.deptno<>dept.deptno
Plan hash value: 4192419542
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 42 |00:00:00.01 | 35 |
| 1 | NESTED LOOPS | | 1 | 42 | 42 |00:00:00.01 | 35 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 11 | 42 |00:00:00.01 | 27 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."DEPTNO"<>"DEPT"."DEPTNO")
已选择21行。