RBO如何确定使用索引的顺序

Oracle的文档上描述过，对于RBO优化器，当同一个表包含了单列非唯一索引和复合索引，Oracle会优先考虑复合索引。

 

不过事实上并不想Oracle文档描述的那么简单，看下面这个测试：
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> CREATE TABLE T1 (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER);
表已创建。
SQL> CREATE INDEX IND_T1_COL1 ON T1(COL1);
索引已创建。
SQL> CREATE INDEX IND_T1_COL23 ON T1(COL2, COL3);
索引已创建。
SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T1 WHERE COL1 = 1 AND COL2 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
2 1 INDEX (RANGE SCAN) OF 'IND_T1_COL1' (NON-UNIQUE)
从执行计划上可以看到，Oracle并没有选择复合索引，而是选择了单列索引，难道Oracle文档的描述有误？
SQL> SELECT * FROM T1 WHERE COL1 = 1 AND COL2 = 1 AND COL3 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
2 1 INDEX (RANGE SCAN) OF 'IND_T1_COL23' (NON-UNIQUE)

这次ORACLE选择了复合索引。看来Oracle并不是简单的选择复合索引。观察第一个查询，对于索引IND_T1_COL1来说，由于指定了COL1列，所以查询包含了索引的全部列。而对于索引IND_T1_COL23来说，由于只包含了COL2列，因此查询仅仅包含了索引的部分列，在这种情况下，Oracle会优先选择包含了全部列的单列索引。
再看第二个查询，由于包含了COL2和COL3列，对于索引IND_T1_COL23来说，也包含了全部列，在这种情况下，Oracle优先使用了复合索引。
再看一个例子：
SQL> CREATE TABLE T2 (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER, COL4 NUMBER, COL5 NUMBER);
表已创建。
SQL> CREATE INDEX IND_T2_COL12 ON T2(COL1, COL2);
索引已创建。
SQL> CREATE INDEX IND_T2_COL345 ON T2(COL3, COL4, COL5);
索引已创建。
SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL3 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL345' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL2 = 1 AND COL3 = 1 ;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL12' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL2 = 1 AND COL3 = 1 AND COL4 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL12' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL3 = 1 AND COL4 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL345' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL2 = 1 AND COL3 = 1 AND COL4 = 1 AND COL5 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL345' (NON-UNIQUE)
对于第一个查询，两个索引的索引列都没有被查询语句完全包含，Oracle选择了IND_T2_COL345。而且观察上面5个查询的结果可以发现，只有索引IND_T2_COL12的索引列完全被包含，且索引IND_T2_COL345的索引列没有完全被包含的情况下，才使用了IND_T2_COL12索引。
对于上面的结果，这里先不下结论。我们再看一个例子，下面把T2删除重建，所有的语句和上面完全一样，唯一的区别是，将两个索引创建的顺序颠倒一下：
SQL> DROP TABLE T2;
表已删除。
SQL> CREATE TABLE T2 (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER, COL4 NUMBER, COL5 NUMBER);
表已创建。
SQL> CREATE INDEX IND_T2_COL345 ON T2(COL3, COL4, COL5);
索引已创建。
SQL> CREATE INDEX IND_T2_COL12 ON T2(COL1, COL2);
索引已创建。
SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL3 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL12' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL2 = 1 AND COL3 = 1 ;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL12' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL2 = 1 AND COL3 = 1 AND COL4 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL12' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL3 = 1 AND COL4 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL345' (NON-UNIQUE)

SQL> SELECT * FROM T2 WHERE COL1 = 1 AND COL2 = 1 AND COL3 = 1 AND COL4 = 1 AND COL5 = 1;
未选定行
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
2 1 INDEX (RANGE SCAN) OF 'IND_T2_COL345' (NON-UNIQUE)
这次的结果和上次有所区别：第一个查询的结果发生了变化。所有的条件都没有变化，而只有索引的创建顺序发生了变化，这说明索引的选择和索引的创建顺序有关。
对RBO如何判断索引的使用顺序推测如下：
首先，根据索引包含的列是否被WHERE语句中完全包括，将索引分为两大类：一类索引被完全包含，一类不被完全包含（还有一类索引，这种索引根本没有被WHERE语句包括，不过这种索引不会被使用，因此这里也就不考虑了）。而完全包含的索引被优先考虑；
如果多个索引都是完全包含索引或者都是不完全包含索引，那么比较索引中被WHERE语句中包含的列的个数，包含的个数越多，越被优先考虑（从这一点可以推出文档上给出的结论：完全包含的复合索引与完全包含的单列索引相比具有优先级）；
如果两个索引都是完全包含索引或者都是不完全包含索引，且包含的列数也相同的话，那么Oracle会优先考虑最后创建的那个索引。
Oracle会根据上面的三个条件的顺序找到优先级最高的索引作为RBO使用的索引。
以上结论仅是个人通过查询结果进行的推断，不保证与Oracle真是情况完全一致。