?

?

我们发现，驱动表变为emp_temp了。

?

说明，当目标SQL有两条或者两条以上的执行路径的等级值相同时，我们确定可以通过改变目标SQL中所涉及的各个对象在该SQL文本中出现的先后顺序来影响RBO对其执行计划的选择。

?

?

注意：以上几个实验都是就RBO模式。RBO不支持HASH join。

?

?

Emp有主键，emp_temp上empno列没有索引。

?

10:43:50 scott@felix SQL>Select t1.mgr,t2.deptno from emp  t1,emp_temp t2
10:44:00   2   Where t1.empno=t2.empno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 367190759

-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          |
|   2 |   NESTED LOOPS               |          |
|   3 |    TABLE ACCESS FULL         | EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP   |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |
-------------------------------------------------

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

   4 - access("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        778  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

10:44:00 scott@felix SQL>

?

?

修改该SQL文本的表顺序：

?

Select t1.mgr,t2.deptno from emp_temp t2,emp  t1
 Where t1.empno=t2.empno;
 
10:44:00 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp t2, emp t1
10:49:57  2    Where t1.empno=t2.empno;
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 367190759
 
-------------------------------------------------
| Id  |Operation                    | Name     |
-------------------------------------------------
|   0 |SELECT STATEMENT             |          |
|   1 |  NESTED LOOPS                |          |
|   2 |   NESTED LOOPS               |         |
|   3 |   TABLE ACCESS FULL         |EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN         | PK_EMP   |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP      |
-------------------------------------------------
 
Predicate Information (identified by operationid):
---------------------------------------------------
 
   4 -access("T1"."EMPNO"="T2"."EMPNO")
 
Note
-----
   - rulebased optimizer used (consider using cbo)
 
 
Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
        22  consistent gets
         0  physical reads
         0  redo size
       778  bytes sent via SQL*Net toclient
       524  bytes received via SQL*Netfrom client
         2  SQL*Net roundtrips to/fromclient
         0  sorts (memory)
         0  sorts (disk)
        14  rows processed
 
10:49:57 scott@felix SQL>

?

?