嵌套循环内连接 可以控制驱动表

嵌套循环 改变驱动表:


 select  /*+ LEADING(e) use_nl(e,d)*/ e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno 

SQL> explain plan for select  /*+ LEADING(e) use_nl(e,d)*/ e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno ;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2694310824

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |    14 |   420 |    17	 (0)| 00:00:01 |
|   1 |  NESTED LOOPS		     |	       |    14 |   420 |    17	 (0)| 00:00:01 |
|   2 |   NESTED LOOPS		     |	       |    14 |   420 |    17	 (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	     | EMP     |    14 |   238 |     3	 (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN	     | DEPT_PK |     1 |       |     0	 (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

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

已选择17行。

 select  /*+ LEADING(d) use_nl(e,d)*/ e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno 


SQL> explain plan for  select  /*+ LEADING(d) use_nl(e,d)*/ e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno ;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4192419542

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

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

   3 - filter("E"."DEPTNO"="D"."DEPTNO")

已选择15行。


嵌套循环内连接的时候,可以随意修改驱动表