----------------------
Plan hash value: 2973289657
----------------------------------------------------
| Id |Operation | Name |
----------------------------------------------------
| 0 |SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP |
|* 2 | INDEX RANGE SCAN | IDX_MGR_TEMP |
----------------------------------------------------
Predicate Information (identified by operationid):
---------------------------------------------------
1 -filter("DEPTNO">100)
2 -access("MGR">100)
Note
-----
- rulebased optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
799 bytes sent via SQL*Net toclient
512 bytes received via SQL*Netfrom client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
10:17:48 scott@felix SQL>
?
?
如上实验可知，当目标SQL有两条或者两条以上的执行路径的登记相同时，我们确实可以通过调整相关对象在数据字典缓存中的缓存顺序来影响RBO对于执行计划的选择。
?
?
?
Create table emp_temp1 as select * from emp;
10:25:30 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp t1,emp_temp1 t2
10:26:27 2 Where t1.empno=t2.empno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1323777565
-----------------------------------------
| Id |Operation | Name |
-----------------------------------------
| 0 |SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| EMP_TEMP1 |
|* 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL| EMP_TEMP |
-----------------------------------------
Predicate Information (identified by operationid):
---------------------------------------------------
4 -access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- rulebased optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
4 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
2 sorts (memory)
0 sorts (disk)
14 rows processed
10:31:02 scott@felix SQL>
?
我们发现emp_tem1p作为驱动表，
注意：排序合并连接，从严格意义上来讲，并没有驱动表和被驱动表的概念，这里只是为了方便阐述而人为的给排序合并连接添加了概念。
?
?
那么改变该SQL的SQL文本中的位置会怎么样呢？？
?
Select t1.mgr,t2.deptno from emp_temp1 t2,emp_temp t1
Where t1.empno=t2.empno;
10:31:02 scott@felix SQL>Selectt1.mgr,t2.deptno from emp_temp1 t2,emp_temp t1
10:36:15 2 Where t1.empno=t2.empno;
MGR DEPTNO
---------- ----------
7902 20
7698 30
7698 30
7839 20
7698 30
7839 30
7839 10
7566 20
10
7698 30
7788 20
7698 30
7566 20
7782 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2135683657
-----------------------------------------
| Id |Operation | Name |
-----------------------------------------
| 0 |SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| EMP_TEMP |
|* 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL| EMP_TEMP1 |
-----------------------------------------
Predicate Information (identified by operationid):
-------------------------