当前位置：首页 -> 基础 -> 数据库编程

hash join、nested loop，sort merge join(三)

2014-11-24 08:46:23 · 作者: · 浏览: 12
标签: hash join nested loop sort merge
Execution Plan
----------------------------------------------------------
Plan hash value: 2114037428
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 56 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 56 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| AMY_EMP | 2 | 50 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | REVERSE_INDEX | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("AMY_EMP"."ENAME"='KING' OR "AMY_EMP"."ENAME"='TURNER')
3 - access("AMY_EMP"."DEPTNO"="AMY_DEPT"."DEPTNO")
驱动表为：amy_emp,被驱动表为：amy_dept,use_nl忽略hint
SQL> select /*+use_nl(amy_dept)*/ empno,ename,job,mgr from amy_dept,amy_emp where amy_emp.deptno=amy_dept.deptno and amy_emp.ename in('KING','TURNER');
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2114037428
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 56 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 56 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| AMY_EMP | 2 | 50 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | REVERSE_INDEX | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("AMY_EMP"."ENAME"='KING' OR "AMY_EMP"."ENAME"='TURNER')
3 - access("AMY_EMP"."DEPTNO"="AMY_DEPT"."DEPTNO")
SQL> select /*+ordered use_nl(amy_dept)*/ empno,ename,job,mgr from amy_dept,amy_emp where amy_emp.deptno=amy_dept.deptno and amy_emp.ename in('KING','TURNER');
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3588874585
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 56 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 56 | 4 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | REVERSE_INDEX | 4 | 12 | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| AMY_EMP | 2 | 50 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("AMY_EMP"."DEPTNO"="AMY_DEPT"."DEPTNO")
3 - filter("AMY_EMP"."ENAME"='KING' OR "AMY_EMP"."ENAME"='TURNER')
SQL>
驱动表为：amy_dept，我们使用了ordered进行了指定。full() index()
3、sort meger join
Hash Join Hints
Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables t