SQL优化之表连接方式

1.嵌套循环(DESTED LOOPS)

Note:嵌套循环被驱动表必须走索引，而且索引只能走INDEX UNIQUE SCAN或者INDEX RANGE SCAN

SQL> select /*+gather_plan_statistics use_nl(e,d) leading(e)*/* from emp e,dept d where d.deptno=e.deptno;

已选择14行。

执行计划

----------------------------------------------------------

Plan hash value: 3625962092

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |    14 |   812 |    17   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |         |       |       |            |

       |

|   2 |   NESTED LOOPS               |         |    14 |   812 |    17   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------

--------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         26  consistent gets

          0  physical reads

          0  redo size

       2035  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

SQL> select /* use_nl(d,e) leading(e) */
* from dept d
left join emp e on d.deptno=e.deptno;

已选择15行。

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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |

| 1 | MERGE JOIN OUTER | | 14 | 812 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

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

4 - access("D"."DEPTNO"="E"."DEPTNO"(+))
filter("D"."DEPTNO"="E"."DEPTNO"(+))

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1916 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed

SQL> select /* use_nl(d,e) leading(e) */
* from dept d
left join emp e on d.deptno=e.deptno
where e.sal<3000;

已选择11行。

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

--------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 7 | 406 | 6 (17)| 00:00:01 |

| 1 | MERGE JOIN | | 7 | 406 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 0000:01 |

|* 4 | SORT JOIN | | 7 | 266 | 4 (25)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | EMP | 7 | 266 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

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

4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
5 - filter("E"."SAL"<3000)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1756 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed

SQL> select /*+leading(e) use_nl(d,e) */
* from dept d
left join emp e on d.deptno=e.deptno
where e.sal<3000;

已选择11行。

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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 7 | 406 | 10 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | | | |
|

| 2 | NESTED LOOPS | | 7 | 406 | 10 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL | EMP | 7 | 266 | 3 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

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

3 - filter("E"."SAL"<3000)
4 - access("D"."DEPTNO"="E"."DEPTNO")

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
1853 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed

SQL> select /*+ordered */
2 a.ename, a.sal,a.deptno,d.dname,c.grade from dept d, salgrade c,emp a
3 where d.deptno=a.deptno and a.sal between c.losal and c.hisal;

已选择14行。

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

| 0 | SELECT STATEMENT | | 42 | 1512 | 14 (8)| 00:00:01|

|* 1 | HASH JOIN | | 42 | 1512 | 14 (8)| 00:00:01|

| 2 | MERGE JOIN CARTESIAN| | 20 | 460 | 10 (0)| 00:00:01

| 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01|

| 4 | BUFFER SORT | | 5 | 50 | 7 (0)| 00:00:01|

| 5 | TABLE ACCESS FULL | SALGRADE | 5 | 50 | 2 (0)| 00:00:01|

| 6 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01
|----------------------------------------------------------------------------------

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

1 - access("D"."DEPTNO"="A"."DEPTNO")
filter("A"."SAL">="C"."LOSAL" AND "A"."SAL"<="C"."HISAL")

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
1215 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed