SQL：如何调整RBO优化器下的执行计划(20181219)

题目：在使用RBO优化器的情况下，我们调整执行计划的手段非常有限，其中有三种方法可以调整执行计划：（1）修改等价的SQL（2）通过调整数据对象在数据字典中的缓存顺序（3）多表连接中调整表的顺序。代码如下：

（1）方案1
---建测试表和索引
DROP TABLE EMP_TEMP;

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);

---设置窗口为RBO优化器模式
alter session set optimizer_mode = 'RULE';

---只显示查询计划
set autotrace traceonly explain;

---目标SQL
SELECT * FROM EMP_TEMP A WHERE A.MGR > 100 AND A.DEPTNO > 100;


---修改后的目标SQL
SELECT * FROM EMP_TEMP A WHERE A.MGR > 100 AND A.DEPTNO + 0 > 100;



（2）方案2
---建测试表
DROP TABLE EMP_TEMP;
CREATE TABLE EMP_TEMP AS SELECT * FROM EMP;

---优先在DEPTNO上建索引
CREATE INDEX IDX_DEPTNO_TEMP ON EMP_TEMP(DEPTNO);
CREATE INDEX IDX_MGR_TEMP ON EMP_TEMP(MGR);

---设置窗口为RBO优化器模式
alter session set optimizer_mode = 'RULE';

---只显示查询计划
set autotrace traceonly explain;

---目标SQL
SELECT * FROM EMP_TEMP A WHERE A.MGR > 100 AND A.DEPTNO > 100;



（3）方案3
---建测试表
DROP TABLE EMP_TEMP;
DROP TABLE EMP_TEMP1;
CREATE TABLE EMP_TEMP AS SELECT * FROM EMP;
CREATE TABLE EMP_TEMP1 AS SELECT * FROM EMP;

---设置窗口为RBO优化器模式
alter session set optimizer_mode = 'RULE';

---只显示查询计划
set autotrace traceonly explain;

---情况1
SELECT * FROM EMP_TEMP A INNER JOIN EMP_TEMP1 B ON A.EMPNO = B.EMPNO;
SELECT * FROM EMP_TEMP1 A INNER JOIN EMP_TEMP B ON A.EMPNO = B.EMPNO;

---情况2
CREATE INDEX IDX_EMPNO_TEMP ON EMP_TEMP(EMPNO);

SELECT * FROM EMP_TEMP A INNER JOIN EMP_TEMP1 B ON A.EMPNO = B.EMPNO;
SELECT * FROM EMP_TEMP1 A INNER JOIN EMP_TEMP B ON A.EMPNO = B.EMPNO;

图1

图2

图3

图4

 

图5

 

解答：

方案1：目标SQL的执行计划如图1，执行计划选择的是IDX_DEPTNO_TEMP索引范围扫描，假如我们发现走索引IDX_MGR_TEMP比走索引IDX_DEPTNO_TEMP效率要高，则可以等价修改目标SQL，执行修改后的SQL，可以看到执行计划选择了：IDX_MGR_TEMP的范围扫描，其中NUMBER类型的可以：+0，VARCHAR2类型的可以：||''。

方案2：由于我们先建了索引IDX_DEPTNO_TEMP，所以在先缓存的索引是IDX_MGR_TEMP，所以图3显示我们的RBO选择了IDX_MGR_TEMP的范围索引。

方案3：情况1中两种SQL的执行计划等级是一样的，所以调整对象表的顺序可以更改执行计划的选择如图4，情况2中由于在EMP_TEMP的EMPNO上建了索引，所以不管对象表的排序如何，都会先选择EMP_TEMP为驱动表，进行索引的ROWID扫描。也就是说调整对象表的顺序只在两种执行计划等级一样的情况下才会实现执行计划的调整。