Hints:使用Leading、Ordered、Swap_join_inputs控制Join顺序

IT那活儿发布于2023-01-11 13:19 / 2793人阅读

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

使用Hints控制表连接顺序

我们知道，对于USE_NL,USE_HASH必须指定leading或ordered，从而保证驱动表的顺序，如果不指定leading或ordered，可能你指定的是USE_HASH，内部给你走成USE_NL。

一般来说，ordered也就是表连接按照 from顺序，leading可以写多个指定顺序leading(a,b,c,d)，含义是 a驱动b,然后结果集驱动c,最后结果集驱动d、也就是((a,b),c),d，但是不一定，见本文后面例子，遇到外连接和HASH JOIN情况比较复杂，需要使用swap_join_inputs才能保证表连接顺序的稳定。

对于hash join可以使用swap_join_inputs和no_swap_join_inputs来改变驱动表顺序（也就是交换驱动表的意思，比leading(a) use_hash(b)一般是a驱动b,如果加上swap_join_inputs(b)则交换a,b驱动表顺序，变为b驱动a,对应的no_swap_join_inputs则是不允许交换顺序，特别是用在hash join right outer情况下。

比如：

</>复制代码 
leading(a,b,c,d) use_hash(a,b,c,d) swap_join_inputs(c) no_swap_join_inputs(d)

其执行计划结果类似如下：
        hash join
          hash_join
               c
               hash_join
                a
                b
           d

Leading(a,b,c,d) use_hash(a,b,c,d)正常的是a,b结果驱动c,但是使用了swap_join_inputs(c),则改变了驱动表顺序，使c来驱动(a,b)，所以执行计划出现上述结果。

对于use_nl则无法通过swap_join_inputs控制顺序复杂的顺序，简单的可以用leading写多个leading(b,a,c) use_nl(a,b,c)，use_nl(a,b,c)相当于 use_nl(a) use_nl(b) use_nl(c) use_hash写多个一样，leading(b,a,c) use_nl(a,b,c)其执行计划如下，b,a的结果做驱动表与c做nested loops：

</>复制代码 
nested loops 
             nested loops
                 b
                 a
              c

但是如果想要实现类似hash join的写法，让c做驱动表，驱动b,a的nested loops,则不行, 只能在SQL写法中t1,t2先关联然后与t3做关联。

以t1,t2,t3分别代表a,b,c做例子：

</>复制代码 
select /*+ ordered use_nl(t3 x) */ *
from t3,
(select /*+ no_merge use_nl(t1, t2) */
  t1.object_id, t1.owner, t1.object_name
  from t1,t2
  where t1.object_id=t2.object_id) X
where x.object_name=t3.object_name;

执行计划如下：

</>复制代码 
-------------------------------
| Operation | Name |
-------------------------------
| SELECT STATEMENT     | |
| NESTED LOOPS | |
| TABLE ACCESS FULL  | T3 |
| VIEW               | |
| NESTED LOOPS | |
| TABLE ACCESS FULL| T1 |
| TABLE ACCESS FULL| T2 |
-------------------------------

下面研究下外连接中如何改变表连接顺序：

</>复制代码 
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
DROP TABLE d;
CREATE TABLE a AS SELECT * FROM All_Objects;
CREATE TABLE b AS SELECT * FROM All_Objects WHERE ROWNUM<100;
CREATE TABLE c AS SELECT * FROM All_Objects WHERE ROWNUM<200;
CREATE TABLE d AS SELECT * FROM all_objects WHERE ROWNUM<10;

BEGIN
  dbms_stats.gather_table_stats(ownname => USER,tabname => a,cascade => TRUE);
  dbms_stats.gather_table_stats(ownname => USER,tabname => b,cascade => TRUE);
  dbms_stats.gather_table_stats(ownname => USER,tabname => c,cascade => TRUE);
  dbms_stats.gather_table_stats(ownname => USER,tabname => d,cascade => TRUE);
END;
/

SELECT * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

--leading指明a,c的结果集做驱动，驱动d,然后结果集驱动b,但是对于hash_join,可能走right join,所以加上no_swap_join_inputs.

</>复制代码 
SELECT/*+leading(a,c,d,b) use_hash(a,b,c,d) no_swap_join_inputs(b) no_swap_join_inputs(d) */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

执行计划如下所示：

</>复制代码 
----------------------------------------------------------
Plan hash value: 2471173529
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 74150 |    22M| |  2265   (1)| 00:00:28 |
|* 1 |  HASH JOIN OUTER | | 74150 | 22M|    18M| 2265 (1)| 00:00:28 |
|* 2 | HASH JOIN OUTER |      | 74150 |    17M| 13M|  1343   (1)| 00:00:17 |
|* 3 |    HASH JOIN OUTER | | 74150 | 12M|  7896K| 685 (1)| 00:00:09 |
|   4 | TABLE ACCESS FULL| A | 74150 |  7023K| |   296   (1)| 00:00:04 |
| 5 |     TABLE ACCESS FULL| C |   199 | 15323 |       | 3 (0)| 00:00:01 |
|   6 | TABLE ACCESS FULL | D | 9 |   675 | |     3   (0)| 00:00:01 |
| 7 |   TABLE ACCESS FULL | B |    99 | 7425 |       | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

下面的虽然swap_join_inputs(b)写在前面，但是leading里的d在b前面，所以d先做swap,然后b,最终b是第一驱动表：

</>复制代码 
SELECT/*+leading(a,c,d,b) use_hash(a,b,c,d) swap_join_inputs(b) swap_join_inputs(d) */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       | | 74150 | 22M| | 693   (1)| 00:00:09 |
|* 1 | HASH JOIN RIGHT OUTER | | 74150 | 22M| | 693   (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL    | B | 99 | 7425 | | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER| | 74150 | 17M| | 689   (1)| 00:00:09 |
| 4 | TABLE ACCESS FULL   | D | 9 | 675 | | 3   (0)| 00:00:01 |
|* 5 | HASH JOIN OUTER     | | 74150 | 12M| 7896K| 685   (1)| 00:00:09 |
| 6 | TABLE ACCESS FULL  | A | 74150 | 7023K| | 296   (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL  | C | 199 | 15323 | | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

通过ordered和swap_join_inputs使a,b先做join：

</>复制代码 
SELECT/*+ordered use_hash(c) swap_join_inputs(c)*/  COUNT(*) FROM
b,c,a,d
 WHERE a.object_id=b.object_id(+)
 AND b.object_id=c.object_id(+)
 AND a.object_id=d.object_id(+);
Execution Plan
----------------------------------------------------------
Plan hash value: 348719292

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT        | | 1 | 15 | 306   (2)| 00:00:04 |
| 1 | SORT AGGREGATE         | | 1 | 15 | | |
|* 2 | HASH JOIN RIGHT OUTER | | 73811 | 1081K| 306   (2)| 00:00:04 |
| 3 | TABLE ACCESS FULL    | D | 9 | 27 | 3   (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER| | 73811 | 864K| 302   (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL   | C | 199 | 796 | 3   (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER     | | 73811 | 576K| 298   (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL  | A | 73811 | 360K| 294   (1)| 00:00:04 |
| 8 | TABLE ACCESS FULL  | B | 99 | 297 | 3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

2 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
4 - access("B"."OBJECT_ID"="C"."OBJECT_ID"(+))
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))

--下面研究下ordered和leading配合swap_join_inputs改变外连接表连接顺序的复杂例子：

</>复制代码 
dingjun123@ORADB> select count(*) from a;
  COUNT(*)
----------
     74277
已选择 1 行。
已用时间: 00: 00: 00.01
dingjun123@ORADB> select count(*) from b;
  COUNT(*)
----------
        99
已选择 1 行。
已用时间: 00: 00: 00.00
dingjun123@ORADB> select count(*) from c;
  COUNT(*)
----------
       199
已选择 1 行。
已用时间: 00: 00: 00.01
dingjun123@ORADB> select count(*) from d;
  COUNT(*)
----------
         9
已选择 1 行。
已用时间: 00: 00: 00.01
dingjun123@ORADB> set autotrace traceonly exp
dingjun123@ORADB> SELECT * FROM
  2  a,b,c,d
  3  WHERE a.object_id=b.object_id(+)
  4      AND a.object_id=c.object_id(+)
  5      AND a.object_id=d.object_id(+);
已用时间: 00: 00: 00.00
执行计划
Plan hash value: 2669012812

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73811 | 22M|   306   (2)| 00:00:04 |
|* 1 |  HASH JOIN RIGHT OUTER | | 73811 | 22M|   306   (2)| 00:00:04 |
| 2 |   TABLE ACCESS FULL | C |   199 | 15522 |     3   (0)| 00:00:01 |
|* 3 |   HASH JOIN RIGHT OUTER | | 73811 | 17M|   303   (2)| 00:00:04 |
| 4 |    TABLE ACCESS FULL | B |    99 | 7425 |     3   (0)| 00:00:01 |
|* 5 |    HASH JOIN RIGHT OUTER| | 73811 | 12M|   299   (2)| 00:00:04 |
| 6 |     TABLE ACCESS FULL | D |     9 | 675 |     3   (0)| 00:00:01 |
| 7 |     TABLE ACCESS FULL | A | 73811 | 6991K|   295   (1)| 00:00:04 |
--------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
5 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))

--下面使用leading只能控制a,c的顺序，b,d无法控制，由于swap_join_inputs(c)则以c驱动a。

--如何控制b,d的顺序呢？

详细见后面例子：

</>复制代码 
SELECT/*+ leading(a) use_hash(c) swap_join_inputs(c) use_hash(c) use_hash(d) */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 136456331

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT        | | 73811 | 22M| 306   (2)| 00:00:04 |
|* 1 | HASH JOIN RIGHT OUTER  | | 73811 | 22M| 306   (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL     | B | 99 | 7425 | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 73811 | 17M| 303   (2)| 00:00:04 |
| 4 | TABLE ACCESS FULL    | D | 9 | 675 | 3   (0)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER| | 73811 | 12M| 299   (2)| 00:00:04 |
| 6 | TABLE ACCESS FULL   | C | 199 | 15522 | 3   (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL   | A | 73811 | 6991K| 295   (1)| 00:00:04 |
--------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
5 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))

上面的例子不能控制b,d表的顺序，ordered和leading相比还是有灵活性的一面，下面使用ordered配合swap_join_inputs控制复杂表连接顺序。

1）使用ordered,先c,a做hash right,然后b使用no_swap_join_inputs,则c,a的结果作为hash表。

顺序如下：d--->((c--->a)---->b)

</>复制代码 
SELECT/*+ ordered use_hash(c) swap_join_inputs(c) use_hash(b) no_swap_join_inputs(b) use_hash(d) */ * FROM
a,c,b,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 2249876107

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT        | | 73811 | 22M| | 961   (1)| 00:00:12 |
|* 1 | HASH JOIN RIGHT OUTER  | | 73811 | 22M| | 961   (1)| 00:00:12 |
| 2 | TABLE ACCESS FULL     | D | 9 | 675 | | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN OUTER       | | 73811 | 17M| 13M| 957   (1)| 00:00:12 |
|* 4 | HASH JOIN RIGHT OUTER| | 73811 | 12M| | 299   (2)| 00:00:04 |
| 5 | TABLE ACCESS FULL   | C | 199 | 15522 | | 3   (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL   | A | 73811 | 6991K| | 295   (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL    | B | 99 | 7425 | | 3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
4 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))

2）与1）不同的是这里增加了no_swap_join_inputs(d),则d是hash join被驱动的。

顺序如下：((c--->a)---->b)---->d

</>复制代码 
SELECT/*+ ordered use_hash(c) swap_join_inputs(c) use_hash(b) no_swap_join_inputs(b) use_hash(d) no_swap_join_inputs(d)*/ * FROM
a,c,b,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
Execution Plan
----------------------------------------------------------
Plan hash value: 3863684830

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT        | | 73811 | 22M| | 1877   (1)| 00:00:23 |
|* 1 | HASH JOIN OUTER        | | 73811 | 22M| 18M| 1877   (1)| 00:00:23 |
|* 2 | HASH JOIN OUTER       | | 73811 | 17M| 13M| 957   (1)| 00:00:12 |
|* 3 | HASH JOIN RIGHT OUTER| | 73811 | 12M| | 299   (2)| 00:00:04 |
| 4 | TABLE ACCESS FULL   | C | 199 | 15522 | | 3   (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL   | A | 73811 | 6991K| | 295   (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL    | B | 99 | 7425 | | 3   (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL     | D | 9 | 675 | | 3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
2 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))

3）这里没有使用no_swap_join_inputs,优化器根据统计信息以d,b表做hash join right join.

顺序如下：d--->(b---->(c---->a))

</>复制代码 
SELECT/*+ ordered use_hash(c) swap_join_inputs(c) use_hash(b) use_hash(d) */ * FROM
a,c,b,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 1239593472

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT        | | 73811 | 22M| 306   (2)| 00:00:04 |
|* 1 | HASH JOIN RIGHT OUTER  | | 73811 | 22M| 306   (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL     | D | 9 | 675 | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 73811 | 17M| 303   (2)| 00:00:04 |
| 4 | TABLE ACCESS FULL    | B | 99 | 7425 | 3   (0)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER| | 73811 | 12M| 299   (2)| 00:00:04 |
| 6 | TABLE ACCESS FULL   | C | 199 | 15522 | 3   (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL   | A | 73811 | 6991K| 295   (1)| 00:00:04 |
--------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
5 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))

--使用ordered本来是hash join(a,b,c,d)，因为使用了swap_join_inputs(b)也就是变成了hash join(b,a),使用了swap_join_inputs(c)变成了hash join(c(hash join(b,a)),使用了swap_join_inputs(d)变成了hash join(d (hash join(c(hash join(b,a))))，见下例：

</>复制代码 
SELECT/*+ ordered use_hash(b) swap_join_inputs(b) use_hash(c)
 swap_join_inputs(c) use_hash(d) swap_join_inputs(d)*/ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

其执行计划是：
Execution Plan
----------------------------------------------------------
Plan hash value: 1261491594

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT        | | 73811 | 22M| 306   (2)| 00:00:04 |
|* 1 | HASH JOIN RIGHT OUTER  | | 73811 | 22M| 306   (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL     | D | 9 | 675 | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 73811 | 17M| 303   (2)| 00:00:04 |
| 4 | TABLE ACCESS FULL    | C | 199 | 15522 | 3   (0)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER| | 73811 | 12M| 299   (2)| 00:00:04 |
| 6 | TABLE ACCESS FULL   | B | 99 | 7425 | 3   (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL   | A | 73811 | 6991K| 295   (1)| 00:00:04 |
--------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))
5 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))

ORDERED注意点：

--ordered貌似是建议join的顺序按from顺序，但是不一定，但是第1个表肯定是驱动表，遇到hash join right outer时候可能后面的表连接顺序不按照ordered。

比如下面的c驱动a,b结果。

</>复制代码 
SELECT/*+ ordered */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 4024647441

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       | | 73811 | 22M| | 689   (1)| 00:00:09 |
|* 1 | HASH JOIN RIGHT OUTER | | 73811 | 22M| | 689   (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL    | D | 9 | 675 | | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER| | 73811 | 17M| | 685   (1)| 00:00:09 |
| 4 | TABLE ACCESS FULL   | C | 199 | 15522 | | 3   (0)| 00:00:01 |
|* 5 | HASH JOIN OUTER     | | 73811 | 12M| 7864K| 681   (1)| 00:00:09 |
| 6 | TABLE ACCESS FULL  | A | 73811 | 6991K| | 295   (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL  | B | 99 | 7425 | | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))
5 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))

--leading单表指定驱动表，但是后面有swap_join_inputs,优化器会选择，比如下例中的d是一个驱动表，虽然leading(a) use_hash(d)但是使用了swap_join_inputs(d)则交换a,d 驱动表顺序。

</>复制代码 
SELECT/*+ leading(a) use_hash(d) swap_join_inputs(d)*/ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
Execution Plan
----------------------------------------------------------
Plan hash value: 2669012812

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT        | | 73811 | 22M| 306   (2)| 00:00:04 |
|* 1 | HASH JOIN RIGHT OUTER  | | 73811 | 22M| 306   (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL     | C | 199 | 15522 | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 73811 | 17M| 303   (2)| 00:00:04 |
| 4 | TABLE ACCESS FULL    | B | 99 | 7425 | 3   (0)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER| | 73811 | 12M| 299   (2)| 00:00:04 |
| 6 | TABLE ACCESS FULL   | D | 9 | 675 | 3   (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL   | A | 73811 | 6991K| 295   (1)| 00:00:04 |
--------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
5 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))

--leading指定多表，表之间是外连接，需要使用(no_)swap_join_inputs来控制连接顺序，否则不一定按照指定写法做表连接。比如下面的leading(b,a,c,d) use_hash(a,c,d) swap_join_inputs(a)本来按照leading(b,a,c,d)应该是b做驱动表，因为指定swap_join_inputs(a)所以以a作为驱动表。

这里其实可以更复杂，可以增加swap_join_inputs(b),no_swap_join_inputs等，有兴趣的可以进行测试。

</>复制代码 
dingjun123@ORADB> SELECT/*+ leading(b,a,c,d) use_hash(a,c,d) swap_join_inputs(a)*/ * FROM
2  a,b,c,d
3  WHERE a.object_id=b.object_id(+)
4      AND a.object_id=c.object_id(+)
5      AND a.object_id=d.object_id(+);
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1247046137

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73811 | 22M| | 689   (1)| 00:00:09 |
|* 1 | HASH JOIN RIGHT OUTER | | 73811 | 22M| | 689   (1)| 00:00:09 |
| 2 | TABLE ACCESS FULL | B | 99 | 7425 | | 3   (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER| | 73811 | 17M| | 685   (1)| 00:00:09 |
| 4 | TABLE ACCESS FULL | D | 9 | 675 | | 3   (0)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 73811 | 12M| 7864K| 681   (1)| 00:00:09 |
| 6 | TABLE ACCESS FULL | A | 73811 | 6991K| | 295   (1)| 00:00:04 |
| 7 | TABLE ACCESS FULL | C | 199 | 15522 | | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
3 - access("A"."OBJECT_ID"="D"."OBJECT_ID"(+))
5 - access("A"."OBJECT_ID"="C"."OBJECT_ID"(+))

总结：

表连接顺序是表之间能够高效使用Join算法的基础(NESTED LOOPS、HASH JOIN、SORT MERGE JOIN)，有时候遇到优化器BUG、功能不完善的情况下，需要使用Hints来进行控制。

当然，最好不要在原SQL上使用Hints,我们可以通过SQL PROFILE等方式配合HINTS来对SQL进行优化，这样不用修改原有SQL达到优化的目的。

本文作者：丁俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

GPU云服务器云服务器顺序控制 Ordered 管理控制台使用版本控制git的使用

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129287.html

宜信-运维-数据库|SQL优化：一篇文章说清楚Oracle Hint的正确使用姿势

摘要：引导优化器按照哈希扫描的方式从表中读取数据。告诉优化器强制选择位图索引。这个提示会使优化器合并表上的多个位图索引，而不是选择其中最好的索引这是提示的用途。还可以使用指定单个索引对于指定位图索引，该提示优先于提示。一、提示（Hint）概述 1、为什么引入Hint? Hint是Oracle数据库中很有特色的一个功能，是很多DBA优化中经常采用的一个手段。那为什么Oracle会考虑引入优化...

LeoHsiun 2019-06-25 19:17 评论0 收藏0
基于AspectJ的Spring AOP Advice执行顺序

摘要：前言用过做过开发的同学多少都对的有所了解和使用的经验也都知道有等至于的基本概念我想大家也都清楚这里也就不再赘述今天在论坛里看到了一个问题谈到了的执行顺序的问题看到问题以后突然发现自己对这方面的理解也不是十分的深入在回答问题的同时正好对这个知前言用过Spring做过开发的同学,多少都对Spring的AOP有所了解和使用的经验.也都知道有@Around,@Before,@After等A...

xinhaip 2019-08-16 10:48 评论0 收藏0
Java实战之Java8指南

摘要：首先我们定义一个有两个不同控制器的然后，我们创建一个特定的工厂接口来创建新的对象不需要手动的去继承实现该工厂接口，我们只需要将控制器的引用传递给该接口对象就好了的控制器会自动选择合适的构造器方法。这种指向时间轴的对象即是类。本文为翻译文章，原文地址这里欢迎来到本人对于Java 8的系列介绍教程，本教程会引导你一步步领略最新的语法特性。通过一些简单的代码示例你即可以学到默认的接口方...

nemo 2019-08-14 12:50 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1484·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1839·2023-01-11 13:20
ORA-600处理一例

阅读 1284·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 2029·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 4238·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2915·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1533·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3835·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

Hints:使用Leading、Ordered、Swap_join_inputs控制Join顺序

使用Hints控制表连接顺序

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

总结：

表连接顺序是表之间能够高效使用Join算法的基础(NESTED LOOPS、HASH JOIN、SORT MERGE JOIN)，有时候遇到优化器BUG、功能不完善的情况下，需要使用Hints来进行控制。

当然，最好不要在原SQL上使用Hints,我们可以通过SQL PROFILE等方式配合HINTS来对SQL进行优化，这样不用修改原有SQL达到优化的目的。

本文作者：丁 俊（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

相关文章

发表评论

0条评论

男|高级讲师

TA的文章

最新活动

本文作者：丁俊（上海新炬王翦团队）