/*
理解Rule-based 优化器
RBO 优先使用预定义方法去计算使用的某条路径访问数据库资料.
RDBMS 使用下列条件则优先选择RBO的优化器.包括:
在init.ora 文件中设定OPTIMIZER_MODE = RULE
在init.ora 文件中设定OPTIMIZER_MODE = choose,并且在SQL 中所包含的Table 没有统计资料.
在Session 中使用 alter session set optimizer_mode=rule
在Session 中使用 Alter session set optomizer_mode=Choose 命令,并且SQL 中的任何table 都没有统计资料
Rule hint (例如 select /*+RULE*/....) 在SQL 中强制使用RBO
*/
---RBO rule-based optimizer
--(EMP_NO,EMP_NAME,EMP_CLASS,DEPT_NO,EMP_CATEGORY,COST_CENTER)
--Case 1: (In fact, only the two-column index is used;
-- the single-column index is not used. While Oracle will merge two single-column indexes,
-- it will not merge a multi-column index with another index. )
ALTER session SET optimizer_mode=rule ;
CREATE INDEX idx_1 ON emp(dept_no) ;
CREATE INDEX idx_2 ON emp(emp_no,emp_name);
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
AND emp_name = 'PJWANG'
AND dept_no = '12'
---未Create Index 时
-- Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 TABLE ACCESS (FULL) OF 'EMP'
-- Create index idx_1 (dept_no)
--( 上面SQL where subclause 怎么组合都会用到 Idx_1)
CREATE INDEX idx_1 ON emp(dept_no) ;
--Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
-- 2 1 INDEX (RANGE SCAN) OF 'IDX_1' (NON-UNIQUE)
CREATE INDEX idx_2 ON emp(emp_no,emp_name);
--(上面的SQL 语句会用到idx_2,因为Idx_2 的columns 比较多且接近Where 后面的 栏位)
--Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
-- 2 1 INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE)
--Case 2:
---(如果index不是所有Column 在where 子句中,sql 则选择一些Column 在同一个Index 中的Index)
--( Lis: 如果index 中的所有栏位在SQL 上,那肯定选择此index,如果此index 包含其他栏位,而这些栏位没有在
-- SQL 中.则SQL 选择Where 条件后尽可能多的栏位在同一个Index's index)
CREATE INDEX idx_3 ON emp(emp_no,emp_name,dept_no,cost_center);
/*
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
AND emp_name = 'PJWANG'
AND dept_no = '12' --AND cost_center='RD Center'
*/
-- Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
-- 2 1 INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE) --Idx_3
--Case 3: (多个Index 都包含 Where 相同个数的Column,则SQL 选择 最后一个创建的index)
-- CREATE INDEX idx_2 ON emp(emp_no,emp_name);
--drop index idx_3.
CREATE INDEX idx_4 ON emp(dept_no,cost_center);
--(idx_2,idx_4 的栏位都在SQL 中,且个数相同,且不会因为Where 后面Column 顺序改变而选择不同的Index)
-- Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
-- 2 1 INDEX (RANGE SCAN) OF 'IDX_4' (NON-UNIQUE)
---Case 4: (如果Where 子句中的多个Column 在 不同的 Index 中, "=" 操作的Index 将替换 如Like,between 之类的Index)
CREATE INDEX idx_3 ON emp(emp_no,emp_name,dept_no,cost_center);
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
AND emp_name LIKE 'PJWAN%'
AND dept_no = '12'
AND cost_center='RD Center'
--
--Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'IDX_4' (NON-UNIQUE)
----如果是Between 则
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
AND emp_name = 'PJWANG'
AND dept_no BETWEEN '12' AND '20'
AND cost_center='RD Center'
--Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
-- 2 1 INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE)
----如果全部是"="
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
AND emp_name = 'PJWANG'
AND dept_no = '12'
AND cost_center='RD Center'
--Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 INDEX (RANGE SCAN) OF 'IDX_3' (NON-UNIQUE)
--Case 5:( where 子句的多个栏位在不同的index 中,则Where 子句中Column 在Index 占Index 所有column 的百分率高的会被采用)
--( 所有Index 中的栏位都没有全部被Where 子句包含)
-- drop index idx_2;
--drop index idx_1;
CREATE INDEX idx_5 ON emp(emp_no,emp_category);
-- CREATE INDEX idx_3 ON emp(emp_no,emp_name,dept_no,cost_center);
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
AND emp_name = 'PJWANG'
AND dept_no='12'
AND emp_class='IE'
--百分率不同: idx_3=(emp_no+emp_name+dept_no)/4=75%
-- idx_5=(emp_no)/2=50%
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'IDX_3' (NON-UNIQUE)
--- 百分率相同 则选最后一个创建的: idx_3=(emp_no+emp_name)/4=50%
-- idx_5=(emp_no)/2=50%
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
AND emp_name = 'PJWANG'
AND emp_category='HI1'
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'IDX_5' (NON-UNIQUE)
--Case 6: (如果两个Index 包含相同的Column ,SQL在使用时会使用和Where 后面第一个Column 相同并且是index第一个column的 index)
-- CREATE INDEX idx_5 ON emp(emp_no,emp_category);
CREATE INDEX idx_6 ON emp( emp_category,emp_no);
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_no = 2
--此SQL 的第一个栏位在Idx_5 中是第一个,则选中idx_5
--Execution Plan
----------------------------------------------------------
-- 0 SELECT STATEMENT Optimizer=RULE
-- 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
-- 2 1 INDEX (RANGE SCAN) OF 'IDX_5' (NON-UNIQUE)
SELECT emp_no,DEPT_NO,COST_CENTER
FROM emp
WHERE emp_category='HI1'
--此SQL 的第一个栏位在Idx_6 中是第一个,则选中idx_6
--Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'IDX_6' (NON-UNIQUE)