Oracle中组合索引的使用详解[转]

薛8687

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 SQL*Net from client
　　1075 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　16100 rows processed
　　从以上结果可以看出，在访问大量数据的情况下，使用索引确实会导致更高的执行成本，这从statistics部分的逻辑读取数（consistent gets）就可以看出，使用索引导致的逻辑读取数是不使用索引导致的逻辑读的10倍还多。因此，Oracle明智地选择了全表扫描而不是索引扫描。
　　下面，让我们来看看where子句中没有索引前导列的情况：
　　
    测试3）
　　
SQL> select * from t where object_name= 'DEPT';
　　已选择10行。
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=14 Bytes=1218)
　　1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=29 Card=14 Bytes=1218)
　　2 1 INDEX (SKIP SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=14 Card=14)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　24 consistent gets
　　0 physical reads
　　0 redo size
　　1224 bytes sent via SQL*Net to client
　　503 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　10 rows processed
　　OK！由于只查询了10条数据，即使没有使用前导列，Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描，该语句的成本：
　　SQL> select/**//*+ NO_INDEX(T INDX_T)*/ * from t where object_name= 'DEPT';
　　已选择10行。
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=14 Bytes=1218)
　　1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=14 Bytes=1218)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　375 consistent gets
　　17 physical reads
　　0 redo size
　　1224 bytes sent via SQL*Net to client
　　503 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　10 rows processed
　　正如我们所料，不使用索引所导致的逻辑读（375）确实比使用索引的逻辑读多（24），达到10倍以上。
　　继续我们的测试，现在我们来看看Oracle不选择使用索引的情况：
　　
    测试4）
　　SQL> select * from t where object_name LIKE 'DE%';
　　已选择180行。
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=37 Bytes=3219)
　　1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=37 Bytes=3219)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　386 consistent gets
　　16 physical reads
　　0 redo size
　　12614 bytes sent via SQL*Net to client
　　624 bytes received via SQL*Net from client
　　13 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　180 rows processed
　　这次只选择了180条数据，跟表T中总的数据量29489条相比，显然只是很小的一部分，但是Oracle还是选择了全表扫描，有386个逻辑读。这种情况下，如果我们强制使用索引，情况会怎样呢？
　　SQL> select/**//*+ INDEX(T INDX_T)*/ * from t where object_name LIKE 'DE%';
　　已选择180行。
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=37 Bytes=3219)
　　1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=182 Card=37 Bytes=3219)
　　2 1 INDEX (FULL SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=144 Card=37)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　335 consistent gets
　　0 physical reads
　　0 redo size
　　4479 bytes sent via SQL*Net to client
　　624 bytes received via SQL*Net from client
　　13 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　180 rows processed
　　通过添加提示（hint），我们强制Oracle使用了索引扫描（index full scan），执行了335个逻辑读，比使用全表扫描的时候少了一些。
　　由此可见，Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。