Oracle中组合索引的使用详解
在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点：
    1、 当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；
    2、 在使用Oracle9i之前的基于成本的优化器（CBO）时，
只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到该索引，这取决于优化器计算的使用索引的成本和使用全表扫描的成
本，Oracle会自动选择成本低的访问路径（请见下面的测试1和测试2）；
    3、 从Oracle9i起，Oracle引入了一种新的索引扫描方式——索引跳跃扫描（index skip
scan），这种扫描方式只有基于成本的优化器（CBO）才能使用。这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的
成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引（请见下面的测试3）；
    4、 Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择（请见下面的测试4）。
    关于以上情况，我们分别测试如下：
    我们创建测试表T，该表的数据来源于Oracle的数据字典表all_objects，表T的结构如下：
SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ---------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
表中的数据分布情况如下：
SQL> select object_type,count(*) from t group by object_type;
OBJECT_TYPE COUNT(*)
------------------ ----------
CONSUMER GROUP 20
EVALUATION CONTEXT 10
FUNCTION 360
INDEX 69
LIBRARY 20
LOB 20
OPERATOR 20
PACKAGE 1210
PROCEDURE 130
SYNONYM 16100
TABLE 180
TYPE 2750
VIEW 8600
已选择13行。
SQL> select count(*) from t;
COUNT(*)
----------
29489
    我们在表T上创建如下索引并对其进行分析：
SQL> create index indx_t on t(object_type,object_name);
索引已创建。
SQL> ANALYZE TABLE T COMPUTE STATISTICS
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /
表已分析。
现在让我们编写几条SQL语句来测试一下Oracle优化器对访问路径的选择：
    测试1）
SQL> set autotrace traceonly
SQL> SELECT * from T WHERE OBJECT_TYPE='LOB';
已选择20行。
Execution Plan
----------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=20 Bytes=1740)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=22 Card=20 Bytes=1740)
2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=2 Card=20)
    正如我们所期望的，由于使用了组合索引的前导列并且访问了表中的少量记录，Oracle明智地选择了索引扫描。那么，如果我们访问表中的大量数据时，Oracle会选择什么样的访问路径呢？请看下面的测试：
    测试2）
SQL> SELECT * from T WHERE OBJECT_TYPE='SYNONYM';
已选择16100行。
Execution Plan
----------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=16100 Bytes=1400700)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=16100 Bytes=1400700)
Statistics
----------------------
0 recursive calls
0 db block gets
1438 consistent gets
13 physical reads
0 redo size
941307 bytes sent via SQL*Net to client
12306 bytes received via SQL*Net from client
1075 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16100 rows processed
    很明显，即使使用了组合索引的前导列，但是由于访问了表中的大量数据，Oracle选择了不使用索引而直接使用全表扫描，因为优化器认为全表扫描的成本更低，但事实是不是真的这样的？我们通过增加提示（hint）来强制它使用索引来看看：
SQL> SELECT/**//*+ INDEX (T INDX_T)*/ * from T WHERE OBJECT_TYPE='SYNONYM';
已选择16100行。
Execution Plan
----------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16180 Card=16100 Bytes=1400700)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16180 Card=16100 Bytes=1400700)
2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=80 Card=16100)
Statistics
----------------------
0 recursive calls
0 db block gets
17253 consistent gets
16 physical reads
0 redo size
298734 bytes sent via SQL*Net to client
12306 bytes received via S