多表联立控制nest_loop

通过ordered或者leading可以控制连接顺序如下

select
/*+
  ordered
  use_nl(t2)
  use_nl(t3)
  use_nl(t4)
  */
  * from t1,t2,t3,t4
 where t1.object_id=t2.object_id
 and t2.object_name=t3.object_name
 and t3.owner=t4.owner
 and t4.owner='MYDB'

执行计划

\----------------------------------------------------------
Plan hash value: 4050478457
\-----------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time   |
\-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |    | 2137 |  801K|  305K (2)| 01:01:03 |
|  1 | NESTED LOOPS     |    | 2137 |  801K|  305K (2)| 01:01:03 |
|  2 |  NESTED LOOPS    |    |  52 | 14976 |  304K (2)| 01:00:55 |
|  3 |  NESTED LOOPS    |    | 11651 | 2184K|  152K (2)| 00:30:26 |
|  4 |   TABLE ACCESS FULL| T1  | 11651 | 1092K|  15  (0)| 00:00:01 |
|* 5 |   TABLE ACCESS FULL| T2  |   1 |  96 |  13  (0)| 00:00:01 |
|* 6 |  TABLE ACCESS FULL | T3  |   1 |  96 |  13  (0)| 00:00:01 |
|* 7 |  TABLE ACCESS FULL | T4  |  41 | 3936 |  13  (0)| 00:00:01 |
\-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
\---------------------------------------------------
  5 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  6 - filter("T3"."OWNER"='MYDB' AND
         "T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
  7 - filter("T4"."OWNER"='MYDB')
Note
\-----
  \- dynamic sampling used for this statement

但是我如何控制由谁来做驱动表呢？

比如我想实现t1作为驱动表和t2做NL，然后T3作为驱动表和t1,t2的结果集作NL，然后前面的结果集作为驱动表和T4做NL,如下：

NESTED LOOPS
   NESTED LOOPS
                T3
     NESTED LOOPS
                  T1
                  T2
              T4

解决方案: 这个leading可以做到。参考下列执行计划

select /*+use_nl(t1 t2 t3)*/
count(*) from
ttt1 t1,
ttt2 t2,
ttt3 t3
where t1.table_name=t2.table_name
and t2.table_name=t3.table_name;

COUNT(*)
----------
        20
Execution Plan
----------------------------------------------------------
Plan hash value: 66364841
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    51 |    51   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    51 |            |          |
|   2 |   NESTED LOOPS       |      |    20 |  1020 |    51   (0)| 00:00:01 |
|   3 |    NESTED LOOPS      |      |    20 |   680 |    27   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TTT1 |    20 |   340 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| TTT2 |     1 |    17 |     1   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL | TTT3 |     1 |    17 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("T1"."TABLE_NAME"="T2"."TABLE_NAME")
   6 - filter("T2"."TABLE_NAME"="T3"."TABLE_NAME")

select /*+use_nl(t1 t2 t3) leading(t2 t3 t1)*/
count(*) from
ttt1 t1,
ttt2 t2,
ttt3 t3
where t1.table_name=t2.table_name
and t2.table_name=t3.table_name;

COUNT(*)
----------
        20
Execution Plan
----------------------------------------------------------
Plan hash value: 4222413350
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    51 |    51   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    51 |            |          |
|   2 |   NESTED LOOPS       |      |    20 |  1020 |    51   (0)| 00:00:01 |
|   3 |    NESTED LOOPS      |      |    20 |   680 |    27   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TTT2 |    20 |   340 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| TTT3 |     1 |    17 |     1   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL | TTT1 |     1 |    17 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("T2"."TABLE_NAME"="T3"."TABLE_NAME")
   6 - filter("T1"."TABLE_NAME"="T2"."TABLE_NAME")

select /*+use_nl(t1 t2 t3) leading(t3 t2 t1)*/
count(*) from
ttt1 t1,
ttt2 t2,
ttt3 t3
where t1.table_name=t2.table_name
and t2.table_name=t3.table_name;

 COUNT(*)
----------
        20
Execution Plan
----------------------------------------------------------
Plan hash value: 1893606081
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    51 |    51   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    51 |            |          |
|   2 |   NESTED LOOPS       |      |    20 |  1020 |    51   (0)| 00:00:01 |
|   3 |    NESTED LOOPS      |      |    20 |   680 |    27   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TTT3 |    20 |   340 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| TTT2 |     1 |    17 |     1   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL | TTT1 |     1 |    17 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("T2"."TABLE_NAME"="T3"."TABLE_NAME")
   6 - filter("T1"."TABLE_NAME"="T2"."TABLE_NAME")