HINTS:Using LEADING、ORDERED、SWAP_JOIN_INPUTS Control Join Order

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

  leading可以写多个指定顺序leading(a,b,c,d)---a驱动b,然后结果集驱动c,结果集驱动d  ((a,b),c),d
  对于hash join可以使用swap_join_inputs和no_swap_join_inputs来指定驱动顺序，比如

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

    

  对于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写多个一样

nested loops 
             nested loops
                 b
                 a
             c 

    

  但是如果想要实现类似hash join的写法，让c做驱动表，驱动b,a的nested loops,则不行
只能这样做：

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;

The plan is

-------------------------------
| Operation            | Name |
-------------------------------
| SELECT STATEMENT     |      |
|  NESTED LOOPS        |      |
|   TABLE ACCESS FULL  | T3   |
|   VIEW               |      |
|    NESTED LOOPS      |      |
|     TABLE ACCESS FULL| T1   |
|     TABLE ACCESS FULL| T2   |
-------------------------------

  这种全以一个表为基表的外连接，基表肯定第一次连接。当然可以修改下sql,然后其他的基表可以先做：

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 |
---------------------------------------------------------------------------------------

  这样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(+);

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        |      | 74277 |    22M|   309   (2)| 00:00:04 |
|*  1 |  HASH JOIN RIGHT OUTER  |      | 74277 |    22M|   309   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL     | C    |   199 | 15323 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER |      | 74277 |    17M|   305   (2)| 00:00:04 |
|   4 |    TABLE ACCESS FULL    | B    |    99 |  7425 |     3   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER|      | 74277 |    12M|   301   (1)| 00:00:04 |
|   6 |     TABLE ACCESS FULL   | D    |     9 |   675 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL   | A    | 74277 |  7036K|   297   (1)| 00:00:04 |
--------------------------------------------------------------------------------

--使用leading只能控制a,c的顺序，c,d无法控制
--ordered和leading相比还是有灵活性的一面
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(+);

--使用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(+);

---((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(+);
   
   
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(+);

--swap_join_inputs和ordered连用，貌似可以用多次（和leading连用好像也可以）
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(+);
  
--ordered貌似是建议join的顺序按from顺序，但是不一定，但是第1个表肯定是驱动表
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(+);

--leading单表指定驱动表，但是后面有swap,oracle会选择，d肯定也是一个驱动表，可能d,a结合，也可能其他的
--leading指定多表，oracle是备选驱动表，也就是驱动表肯定是在这个里面，但是如果指定swap则不一定
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(+);