社区首页 >专栏 >Oracle SQL 性能调优：使用Hint固定执行计划1(Hash Join)

Oracle SQL 性能调优：使用Hint固定执行计划1(Hash Join)

SQLplusDB

发布于 2022-08-19 12:17:05

1.1K00

代码可运行

文章被收录于专栏：Oracle数据库技术Oracle数据库技术

运行总次数：0

代码可运行

编者按：

本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。

Hash Join 是不使用索引等价结合时，最有效的结合方式，所以，使用的机会非常之多。

Hash Join 是在内存中作出 Hash Table 用来存放结合数据。通常，会先访问数据量少的表，之后再访问数据量多的表，这样能保证性能。

先访问的表叫做 Build表，第二个访问的表叫做 Probe表。那么假如 3个以上的表进行 Hash Join 时，各表之间的结合顺序，以及那个表作为 Build表，那个表作为 Probe表，如何来控制呢。

下面我们介绍一下相关的 Hint 来进行有效的控制。

LEADING Hint （指定 Hash Join 顺序）
SWAP_JOIN_INPUTS Hint（指定 Build 表）
NO_SWAP_JOIN_INPUTS Hint（指定 Probe 表）
USE_HASH (指定使用 Hash Join)

下面用几个例子来做一下展示。

准备：

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);
insert into t1 values (1,1);
insert into t2 values (1,2);
insert into t3 values (1,3);
commit;

Case 1：

结合顺序是 A->B(Build 表)->C

SQL> select /*+ leading(a b c) use_hash(b c) swap_join_inputs(b) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;


        C1         C2         C1         C2         C1         C2
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          2          1          3


Execution Plan
----------------------------------------------------------
Plan hash value: 1184213596


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    78 |     9   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    78 |     9   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    52 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T2   |     1 |    26 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


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


   1 - access("A"."C1"="C"."C1")
   2 - access("A"."C1"="B"."C1")

Case 2：

结合顺序是 A->B(Build表)->C(Build表)

SQL> select /*+ leading(a b c) swap_join_inputs(b) swap_join_inputs(c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;


        C1         C2         C1         C2         C1         C2
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          2          1          3


Execution Plan
----------------------------------------------------------
Plan hash value: 1487401159


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    78 |     9   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    78 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T3   |     1 |    26 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      |     1 |    52 |     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


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


   1 - access("A"."C1"="C"."C1")
   3 - access("A"."C1"="B"."C1")

Case 3：

结合顺序是 A->C(Build表)->B

SQL> select /*+ leading(a c b) USE_HASH(c b) swap_join_inputs(c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;


        C1         C2         C1         C2         C1         C2
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          2          1          3




Execution Plan
----------------------------------------------------------
Plan hash value: 2467348796


----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    78 |     9   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    78 |     9   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    52 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T3   |     1 |    26 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T2   |     1 |    26 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


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


   1 - access("A"."C1"="B"."C1")
   2 - access("A"."C1"="C"."C1")

另外，以上面的例子来说，可以用 C->B->A 的顺序进行 Hash Join 吗。不可以哈，因为 B表和 C表之间没有结合键，这样会产生笛卡尔积。

SQL> select /*+ leading(c b a) USE_HASH(c b) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;


        C1         C2         C1         C2         C1         C2
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          2          1          3




Execution Plan
----------------------------------------------------------
Plan hash value: 2210115829


------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    78 |     9   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    78 |     9   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    52 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T3   |     1 |    26 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T2   |     1 |    26 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T1   |     1 |    26 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------


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


   1 - access("A"."C1"="B"."C1" AND "A"."C1"="C"."C1")
————————————————