【sql调优之执行计划】nested loops join and nested loop join outer

版本：10.2.0.4

 

Nested loops（嵌套循环）看一个例子：

SQL> select b.* from scott.emp a,scott.dept b

  2  where a.deptno = b.deptno

  3  and a.empno = 7369

  4  ;

 

    DEPTNO DNAME          LOC

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

        20 RESEARCH       DALLAS

 

 

Execution Plan

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

Plan hash value: 2385808155

 

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

--------

 

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

me     |

 

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

--------

 

|   0 | SELECT STATEMENT             |         |     1 |    27 |     2   (0)| 00

:00:01 |

 

|   1 |  NESTED LOOPS                |         |     1 |    27 |     2   (0)| 00

:00:01 |

 

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |     7 |     1   (0)| 00

:00:01 |

 

|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 00

:00:01 |

 

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     1   (0)| 00

:00:01 |

 

|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00

:00:01 |

 

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

--------

 

 

Predicate Information (identified by operation id):

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

 

   3 - access("A"."EMPNO"=7369)

   5 - access("A"."DEPTNO"="B"."DEPTNO")

 

 

Statistics

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

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

简单的讲就是在表1中每取一行数据，然后从表2中查找匹配的行，然后再回到表1取下一行，如此循环取下去。那么表1中取得的行越少，表2中查找行越容易则嵌套循环的效率越高。

第一个表也通常称为驱动表（或者外部表），第二个表通常称为内部表。

对nested loops join 的选择或者优化，应该考虑驱动表行的选择性和绝对数量大小，并且内部表要能方便的访问到，例如唯一索引，这种结构很多时候用于一个主数据表和一个关联表（或者字典表等）的关联，而关联表很容易使用与主表关联的字段上的索引访问。

 

上述的执行计划可以看出，先访问表emp，emp作为了驱动表，当然也可以使用dept作为驱动表：

 

SQL> select /*+ leading(b) use_nl(a,b)*/b.* from scott.dept b,scott.emp a

  2  where a.deptno = b.deptno

  3  and a.empno = 7369;

 

    DEPTNO DNAME          LOC

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

        20 RESEARCH       DALLAS

 

 

Execution Plan

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

Plan hash value: 3431005640

 

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

-------

 

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

e     |

 

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

-------

 

|   0 | SELECT STATEMENT             |        |     1 |    27 |     7   (0)| 00:

00:01 |

 

|   1 |  NESTED LOOPS                |        |     1 |    27 |     7   (0)| 00:

00:01 |

 

|   2 |   TABLE ACCESS FULL          | DEPT   |     4 |    80 |     3   (0)| 00:

00:01 |

 

|*  3 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     7 |     1   (0)| 00:

00:01 |

 

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

00:01 |

 

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

-------

 

 

Predicate Information (identified by operation id):

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

 

   3 - filter("A"."DEPTNO"="B"."DEPTNO")

   4 - access("A"."EMPNO"=7369)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

         15  consistent gets

          5  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

 

上述执行计划先访问dept表（全表扫描），然后通过index访问emp表，可以看到后者的nested loops的cost为7，大于前者的cost2，可以初步判断后者的效率不如前者，或者也可以简单的看看2者的逻辑读数量来判断一下优劣。具体的性能优化结合实例再述。

 

扩展一下，两个数据集做nested loops join和两个表相似。

 

Nested loops join outer 则是由于做了外连接而产生的，例如：

SQL> select b.* from scott.emp a,scott.dept b

  2  where a.deptno = b.deptno(+)

  3  and a.empno = 7369;

 

    DEPTNO DNAME          LOC

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

        20 RESEARCH       DALLAS

 

 

Execution Plan

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

Plan hash value: 1858280091

 

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

--------

 

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

me     |

 

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

--------

 

|   0 | SELECT STATEMENT             |         |     1 |    27 |     2   (0)| 00

:00:01 |

 

|   1 |  NESTED LOOPS OUTER          |         |     1 |    27 |     2   (0)| 00

:00:01 |

 

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |     7 |     1   (0)| 00

:00:01 |

 

|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 00

:00:01 |

 

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     1   (0)| 00

:00:01 |

 

|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00

:00:01 |

 

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

--------

 

 

Predicate Information (identified by operation id):

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

 

   3 - access("A"."EMPNO"=7369)

   5 - access("A"."DEPTNO"="B"."DEPTNO"(+))

 

 

Statistics

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

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        533  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

SQL>

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-671097/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-671097/