use_hash,use_merge,use_nl

最新推荐文章于 2023-07-03 11:18:23 发布

断剑1989

最新推荐文章于 2023-07-03 11:18:23 发布

阅读量2.2k

点赞数

分类专栏：数据库文章标签： hint use_hash use_merge use_nl

本文链接：https://blog.csdn.net/djz19890117/article/details/42267937

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

use_hash(table1 table2)指定table1与table2连接以hash方式连接，优化器选择驱动表，我们没法控制。

use_merge(table1 table2)指定table1与table2连接以merge方式连接，合并连接没有驱动表的概念。

use_nl(table1 table2)指定table1与table2连接以nested loop方式连接，优化器选择驱动表，我们没法控制。

下面是实验过程

SQL> select empno,ename,dept.deptno from emp,dept
2 where emp.deptno=dept.deptno
3 /

执行计划
----------------------------------------------------------
Plan hash value: 3074306753

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 168 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 2 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
初始情况下，emp与dept以嵌套循环方式连接

SQL> select /*+use_hash(emp dept)*/ empno,ename,dept.deptno from emp,dept
2 where emp.deptno=dept.deptno
3 /

执行计划
----------------------------------------------------------
Plan hash value: 71037407

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 168 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 4 | 8 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

使用use_hash干预后，从执行计划中看出以散列方式连接；用use_hash(emp dept)尝试以emp为驱动表，

结果不成功，说明use_hash无法人工干预哪张表作为驱动表。

SQL> select /*+use_hash(dept emp)*/ empno,ename,dept.deptno from emp,dept
2 where emp.deptno=dept.deptno
3 /

执行计划
----------------------------------------------------------
Plan hash value: 71037407

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 168 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 4 | 8 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
use_hash(dept emp)和use_hash(emp dept)的执行计划一样，进一步说明use_hash无法人工干预哪张表作为驱动表。

SQL> select /*+use_hash(emp)*/ empno,ename,dept.deptno from emp,dept
2 where emp.deptno=dept.deptno
3 /

执行计划
----------------------------------------------------------
Plan hash value: 3074306753

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 168 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 2 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
使用use_hash(emp)后，发现没有使用散列连接，依然用了初始情况下的嵌套循环，说明use_hash相连接的表必须要写全。

下面是use_nl的实验结果，和use_hash情况类似，就没有再做文字说明。
SQL> select t1.ename,t2.ename from test1 t1,test2 t2
2 where t1.ename=t2.ename
3 /

执行计划
----------------------------------------------------------
Plan hash value: 497311279

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 196 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 14 | 98 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST2 | 14 | 98 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ENAME"="T2"."ENAME")

Note
-----
- dynamic sampling used for this statement

SQL> select /*+use_nl(t1 t2)*/ t1.ename,t2.ename from test1 t1,test2 t2
2 where t1.ename=t2.ename
3 /

执行计划
----------------------------------------------------------
Plan hash value: 2336902100

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 17 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 196 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 14 | 98 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEST2 | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T1"."ENAME"="T2"."ENAME")

Note
-----
- dynamic sampling used for this statement

SQL> select /*+use_nl(t2 t1)*/ t1.ename,t2.ename from test1 t1,test2 t2
2 where t1.ename=t2.ename
3 /

执行计划
----------------------------------------------------------
Plan hash value: 2336902100

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 17 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 196 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 14 | 98 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEST2 | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("T1"."ENAME"="T2"."ENAME")

Note
-----
- dynamic sampling used for this statement

SQL> select /*+use_nl(t2)*/ t1.ename,t2.ename from test1 t1,test2 t2
2 where t1.ename=t2.ename
3 /

执行计划
----------------------------------------------------------
Plan hash value: 3916441650

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 196 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST2 | 14 | 98 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST1 | 14 | 98 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ENAME"="T2"."ENAME")

Note
-----
- dynamic sampling used for this statement