1.基于规则的优化器
1.创建测试环境
--建表
create table emp_temp as select * from emp;
create index idx_mgr_temp on emp_temp(mgr);
create index idx_deptno_temp on emp_temp(deptno);
--查询数据库
select *
from emp_temp
where mgr > 100 and deptno > 10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
11 rows selected.2.测试RBO的执行计划
2.1.查看执行计划，执行SQL1：
select * from emp_temp where mgr > 100 and deptno > 10;scott@ORCLPDB01 2023-04-01 13:54:48> alter session set optimizer_mode = 'RULE';
Session altered.
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-01 13:54:53> set autot trace exp
scott@ORCLPDB01 2023-04-01 13:54:56> select * from emp_temp where mgr > 100 and deptno > 10;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1670750536
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP |
|* 2 | INDEX RANGE SCAN | IDX_DEPTNO_TEMP |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MGR">100)
2 - access("DEPTNO">10)
Note
-----
- rule based optimizer used (consider using cbo)2.2.通过等价改写SQL（加0或者空字符串的方式）修改执行计，划执行SQL2：
select * from emp_temp where mgr > 100 and deptno + 0 > 10;scott@ORCLPDB01 2023-04-01 13:55:12> select * from emp_temp where mgr > 100 and deptno + 0 > 10;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
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 operation id):
---------------------------------------------------
1 - filter("DEPTNO"+0>10)
2 - access("MGR">100)
Note
-----
- rule based optimizer used (consider using cbo)2.3.通过修改索引建立的顺序，调整执行计划（删除现有的索引，再新建索引，即是调整索引在数据字典中的顺序）
scott@ORCLPDB01 2023-04-01 13:59:44> drop index IDX_MGR_TEMP;
Index dropped.
Elapsed: 00:00:00.13
scott@ORCLPDB01 2023-04-01 14:05:45> create index idx_mgr_temp on emp_temp(mgr);
Index created.
Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-01 14:06:02> select * from emp_temp where mgr > 100 and deptno > 10;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
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 operation id):
---------------------------------------------------
1 - filter("DEPTNO">10)
2 - access("MGR">100)
Note
-----
- rule based optimizer used (consider using cbo)2.4.验证改变目标SQL中涉及的对象在该SQL文本中出现的先后顺序，改变执行计划，划执行SQL3和SQL4：
--SQL3
scott@ORCLPDB01 2023-04-01 14:09:47> select t1.mgr,t2.deptno
2 from emp_temp t1, emp_temp1 t2
3 where t1.empno = t2.empno;
Elapsed: 00:00:00.00
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 operation id):
---------------------------------------------------
4 - access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- rule based optimizer used (consider using cbo)
--SQL4
scott@ORCLPDB01 2023-04-01 14:10:27> select t1.mgr,t2.deptno
2 from emp_temp1 t2, emp_temp t1
3 where t1.empno = t2.empno;
Elapsed: 00:00:00.00
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 operation id):
---------------------------------------------------
4 - access("T1"."EMPNO"="T2"."EMPNO")
filter("T1"."EMPNO"="T2"."EMPNO")
Note
-----
- rule based optimizer used (consider using cbo)2.5.通过修改执行路径等级制的大小选择执行计划（emp表empno列存在主键）执行SQL5和SQL6
scott@ORCLPDB01 2023-04-01 14:12:29> select t1.mgr,t2.deptno
2 from emp t1, emp_temp t2
3 where t1.empno = t2.empno;
Elapsed: 00:00:00.01
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)
scott@ORCLPDB01 2023-04-01 14:18:34> select t1.mgr,t2.deptno
2 from emp_temp t2,emp t1
3 where t1.empno = t2.empno;
Elapsed: 00:00:00.00
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)注意：如果RBO仅凭目标SQL各执行路径等级值的大小就可以选择执行计划，无论怎么调整位置，对于该SQL的最终执行计划没有任何影响。
