oracle 嵌套循环联结

用scott/tiger登录。

嵌套循环联结就是一个循环嵌在另一个循环当中。

SQL> set autotrace traceonly
SQL> set linesize 120
SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select empno, ename, dname, loc from emp, dept where emp.deptno = dept.deptno;

已选择14行。


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

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   462 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   462 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


统计信息
----------------------------------------------------------
        652  recursive calls
          0  db block gets
        140  consistent gets
         18  physical reads
          0  redo size
        996  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

emp表是驱动表，dept表是内层表。NESTED LOOPS后的第一个表是驱动表。

改变from后面的表顺序：

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select empno, ename, dname, loc from dept, emp where emp.deptno = dept.deptno;

已选择14行。


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

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   462 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   462 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


统计信息
----------------------------------------------------------
        672  recursive calls
          0  db block gets
        142  consistent gets
         25  physical reads
          0  redo size
        996  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

发现驱动表没变，还是emp。

使用ordered提示来改变驱动表：

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select /*+ ordered use_nl(dept, emp) */ empno, ename, dname, loc from dept, emp where emp.deptno = dept.deptno;

已选择14行。


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   462 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |   462 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |    52 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")


统计信息
----------------------------------------------------------
        672  recursive calls
          0  db block gets
        155  consistent gets
         27  physical reads
          0  redo size
        850  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

此时，consistent gets要高于emp表进行驱动。看来优化器是对的。