共计 6926 个字符，预计需要花费 18 分钟才能阅读完成。
这篇文章给大家分享的是有关 Oracle 中组合索引怎么用的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
Oracle 中组合索引的使用
关键词：组合索引 前导列 基于规则的优化器（RBO） 基于成本的优化器（CBO） 提示（hint）
在 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）。
[@more@]
关于以上情况，我们分别测试如下：
我们创建测试表 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 优化器作出更好的选择。
感谢各位的阅读！关于“Oracle 中组合索引怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
