表里多个字段唯一索引一个表多个索引

转载

mob64ca1407d5aa 2024-03-25 22:02:01

文章标签 表里多个字段唯一索引执行计划 Time EXEC 文章分类 数据仓库大数据

经常看到有人提出这样的疑问，我在同一张表上建立了多个索引，为什么Oracle每次都选择一个，而不能同时利用多个索引呢。一般来说，常见的访问同一张表的两个以上索引，存在三种情况，AND-EQUAL、INDEX HASH JOIN和BITMAP INDEX AND/OR。

此外，还有一个设计上的疑问，如果有A、B、C三个字段，都可能作为查询条件，是建立多个复合索引好，还是建立三个单列的索引。这个问题之所以不好回答是因为和业务或者说和查询的模式有很大的关系，不过如果理解了Oracle什么时候会选择一个以上的索引来访问表，就会对于理解如何设计合理的索引有很大的帮助。

简单介绍一下AND-EQUAL执行计划。

测试表：TABLE T_DOUBLE_IND 并在两个字段上建索引

现在建立了一个测试表和两个索引，下面首先来看看AND-EQUAL执行方式：

SELECT ID, NAME, TYPE FROM T_DOUBLE_IND WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';
 
Execution Plan
| Id  | Operation                   | Name            |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |
|   2 |   AND-EQUAL                 |                 |
|*  3 |    INDEX RANGE SCAN         | IND_DOUBLE_NAME |
|*  4 |    INDEX RANGE SCAN         | IND_DOUBLE_TYPE |
-------------------------------------------------------   3 - access("NAME"='T_DOUBLE_IND')
   4 - access("TYPE"='TABLE')-------------------------------------------------------
note - rule based optimizer used (consider using cbo)

由于指定了两个列，且两个列上都包含索引，Oracle选择了扫描两个索引，并使用了AND-EQUAL执行计划。这种扫描方式是分别通过两个索引获取索引键值对应的ROWID，然后合并两个扫描中相等的ROWID，并通过这个ROWID来扫描表。

不过观察执行计划可以发现，当前的优化模式是RBO。

如果收集一下索引 :

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')

再次运行同样的查询：

Execution Plan
--------------------------------------------------------------------------------------------
| Id| Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|  0| SELECT STATEMENT            |                 |    1 |    37 |     3   (0)| 00:00:01 |
|* 1|  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |    1 |    37 |     3   (0)| 00:00:01 |
|* 2|   INDEX RANGE SCAN          | IND_DOUBLE_NAME |    2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------   1 - filter("TYPE"='TABLE')
   2 - access("NAME"='T_DOUBLE_IND')

可以看到，收集统计信息后，优化模式变为CBO，但是Oracle只选择了一个索引进行扫描。对于当前的情况，NAME列的选择度非常高，因此这种方式的代价最低。

即使通过HINT指定两个索引:

SELECT /*+ INDEX(A IND_DOUBLE_NAME) INDEX(A IND_DOUBLE_TYPE) */ID, NAME, TYPE FROM T_DOUBLE_IND WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

CBO优化器也只是会选择其中一个索引来进行扫描，而自动忽略另一个选择度低的索引。

只有使用AND_EQUAL提示，才能在CBO的情况下使用AND-EQUAL执行计划：

SELECT /*+ AND_EQUAL(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ID, NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

在CBO下，Oracle不会自动选择AND-EQUAL执行计划，这是因为Oracle可以根据直方图来判断一个列上各个值的选择度，但是从统计信息无法获得两个不同的列合AND-EQUAL之后的选择度，这个操作后，如果得到的ROWID记录很少，那么这个查询的效率就会很高，如果AND-EQUAL后得到大量的ROWID，那么查询的效率就会很差，这时应该选择全表扫描或其他的执行计划。由于CBO都是根据统计信息分析得到的结果，而AND-EQUAL的结果对于CBO是未知的，因此CBO不会选择AND-EQAUL这种扫描方式。

如果要Oracle获得这种统计信息，最简单的方法就是建立一个复合索引，Oracle在分析索引列的时候自动会分析两个列的组合情况，从而在查询的时候可以准确的判断是否应该使用这个复合索引。

在两个字段上建复合索引IND_DOU_NAM_TYP

执行查询：SELECT ID, NAME, TYPE FROM T_DOUBLE_IND WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
--------------------------------------------------------------------------------------------
| Id| Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                 |    2 |    74 |     3   (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |    2 |    74 |     3   (0)| 00:00:01 |
|*2 |   INDEX RANGE SCAN          | IND_DOU_NAM_TYP |    2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------   2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')

使用了复合索引

现将NAME全部设为一样： UPDATE T_DOUBLE_IND SET NAME = 'T_DOUBLE_IND' 再执行查询。

执行计划还是一样，因为另一个TYPE列的条件，在复合索引里选择度也比较大，Oracle仍然选择了复合索引扫描。

现将TYPE全部设为一样： UPDATE T_DOUBLE_IND SET TYPE = 'TABLE' 再执行查询。变了：

Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |  7778 |   258K|  2010   (1)| 00:00:29 |
|*  1 |  TABLE ACCESS FULL| T_DOUBLE_IND |  7778 |   258K|  2010   (1)| 00:00:29 |
----------------------------------------------------------------------------------   1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')

这种情况下，使得NAME=’T_DOUBLE_IND’和TYPE=’TABLE’的选择性就很差了，这时Oracle就会自动选择全表扫描来代替索引扫描。

=================================================================================================

简单介绍一下BITMAP索引的AND/OR执行计划。

--首先建立一个测试表：

CREATE TABLE T_DOUBLE_IND  (ID NUMBER, NAME VARCHAR2(30), CONTENTS VARCHAR2(4000));

--创建索引

CREATE INDEX IND_DOUBLE_NAME ON T_DOUBLE_IND (NAME);

CREATE INDEX IND_DOUBLE_TYPE ON T_DOUBLE_IND (TYPE);

现在看如何选择执行计划

--BITMAP索引的AND操作只有在CBO模式下才会启用，因此收集表的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND');

SELECT ID, NAME, TYPE  FROM T_DOUBLE_IND  WHERE NAME ='T_DOUBLE_IND'AND TYPE ='TABLE';

Execution Plan

--------------------------------------------------------------------------------------------
| Id| Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                 |    1 |    37 |     3   (0)| 00:00:01 |
|*1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |    1 |    37 |     3   (0)| 00:00:01 |
|*2 |   INDEX RANGE SCAN          | IND_DOUBLE_NAME |    2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

   1 - filter("TYPE"='TABLE')
   2 - access("NAME"='T_DOUBLE_IND')

由于根据当前的列的统计情况分析，使用NAME列上的索引代价最小，因此Oracle选择了IND_DOUBLE_NAME索引扫描。

※ 通过INDEX_COMBINE提示来指定索引进行BITMAP AND/OR操作：

SELECT /*+ INDEX_COMBINE(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ID, NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
--------------------------------------------------------------------------------------------
|Id| Operation                        | Name            |Rows|Bytes| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                 |                 |   1|   37|     5   (0)| 00:00:01 |
| 1|  TABLE ACCESS BY INDEX ROWID     | T_DOUBLE_IND    |   1|   37|     5   (0)| 00:00:01 |
| 2|   BITMAP CONVERSION TO ROWIDS    |                 |    |     |            |          |
| 3|    BITMAP AND                    |                 |    |     |            |          |
| 4|     BITMAP CONVERSION FROM ROWIDS|                 |    |     |            |          |
|*5|      INDEX RANGE SCAN            | IND_DOUBLE_NAME |    |     |     1   (0)| 00:00:01 |
| 6|     BITMAP CONVERSION FROM ROWIDS|                 |    |     |            |          |
|*7|      INDEX RANGE SCAN            | IND_DOUBLE_TYPE |    |     |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
   5 - access("NAME"='T_DOUBLE_IND')
   7 - access("TYPE"='TABLE')

使用INDEX_COMBINE后，Oracle将两个BTREE索引首先转化为BITMAP索引，然后执行BITMAP AND或BITMAP OR的操作，根据需要决定是否再将BITMAP索引转化回BTREE索引，然后根据ROWID访问表得到最终的结果。

※ 并非这种情况下一定需要提示，Oracle会自动根据统计信息来判断，是否应该进行BITMAP索引的转化：

--改变下实际数据统计
UPDATE T_DOUBLE_IND SET NAME = 'T_DOUBLE_IND' WHERE TYPE = 'SYNONYM';
--22600 rows updated.

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')

SELECT ID, NAME, TYPE FROM T_DOUBLE_IND WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
--------------------------------------------------------------------------------------------
|Id| Operation                        | Name            |Rows|Bytes| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                 |                 |2597|85701|  1219   (1)| 00:00:18 |
| 1|  TABLE ACCESS BY INDEX ROWID     | T_DOUBLE_IND    |2597|85701|  1219   (1)| 00:00:18 |
| 2|   BITMAP CONVERSION TO ROWIDS    |                 |    |     |            |          |
| 3|    BITMAP AND                    |                 |    |     |            |          |
| 4|     BITMAP CONVERSION FROM ROWIDS|                 |    |     |            |          |
|*5|      INDEX RANGE SCAN            | IND_DOUBLE_TYPE |8456|     |    12   (0)| 00:00:01 |
| 6|     BITMAP CONVERSION FROM ROWIDS|                 |    |     |            |          |
|*7|      INDEX RANGE SCAN            | IND_DOUBLE_NAME |8456|     |    75   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------
   5 - access("TYPE"='TABLE')
   7 - access("NAME"='T_DOUBLE_IND')

在这个例子中，由于两个索引的选择性都很差，而基表本身由于存在一个长度为1000的列，因此全表扫描也是比较低效的，所以Oracle选择通过两个索引进行BITMAP AND操作来获取记录。

对于当前的查询而已，这个选择是很高效的，但是可以明显的看到，Oracle的执行计划中，预计返回行数以及COST值，都是十分不准确的。

※ 而对于OR查询条件的情况，一般来说Oracle会根据统计信息来判断是否选择使用BITMAP OR执行计划：

SELECT ID, NAME, TYPEFROM T_DOUBLE_IND WHERE NAME = 'T' OR TYPE = 'CONTEXT';

ID         NAME                           TYPE
---------- ------------------------------ ------------------------------
9766       LT_CTX                         CONTEXT
51595      T                              TABLE
70782      T                              TABLE
48576      EM_GLOBAL_CONTEXT              CONTEXT
48577      EM_USER_CONTEXT                CONTEXT
75651      T                              TABLE
75700      T                              TABLE
7047       REGISTRY$CTX                   CONTEXT
40742      DR$APPCTX                      CONTEXT
56564      T                              TABLE
10 rows selected.

Execution Plan
--------------------------------------------------------------------------------------------
|Id| Operation                        | Name            |Rows|Bytes| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                 |                 |  21|  714|    12   (0)| 00:00:01 |
| 1|  TABLE ACCESS BY INDEX ROWID     | T_DOUBLE_IND    |  21|  714|    12   (0)| 00:00:01 |
| 2|   BITMAP CONVERSION TO ROWIDS    |                 |    |     |            |          |
| 3|    BITMAP OR                     |                 |    |     |            |          |
| 4|     BITMAP CONVERSION FROM ROWIDS|                 |    |     |            |          |
|*5|      INDEX RANGE SCAN            | IND_DOUBLE_NAME |    |     |     1   (0)| 00:00:01 |
| 6|     BITMAP CONVERSION FROM ROWIDS|                 |    |     |            |          |
|*7|      INDEX RANGE SCAN            | IND_DOUBLE_TYPE |    |     |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
   5 - access("NAME"='T')
   7 - access("TYPE"='CONTEXT')

如果指定返回的结果集比较大，则Oracle不会倾向利用索引：

SELECT ID, NAME, TYPE FROM T_DOUBLE_IND WHERE NAME = 'T_DOUBLE_IND' OR TYPE = 'INDEX';

Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 29745 |   987K|  2010   (1)| 00:00:29 |
|*  1 |  TABLE ACCESS FULL| T_DOUBLE_IND | 29745 |   987K|  2010   (1)| 00:00:29 |
----------------------------------------------------------------------------------
   1 - filter("NAME"='T_DOUBLE_IND' OR "TYPE"='INDEX')

※ 不过BITMAP索引天生时候回答COUNT(*)的问题，如果只是查询记录数，则CBO倾向于利用索引，而和索引的选择性没有关系：

SELECT COUNT(*) FROM T_DOUBLE_IND WHERE NAME = 'T_DOUBLE_IND' OR TYPE = 'INDEX';
  

COUNT(*)
----------
33058

Execution Plan
--------------------------------------------------------------------------------------------
|Id| Operation                        | Name            | Rows|Bytes | Cost (%CPU)|Time    |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                 |                 |    1|   29 |    87   (2)|00:00:02|
| 1|  SORT AGGREGATE                  |                 |    1|   29 |            |        |
| 2|   BITMAP CONVERSION COUNT        |                 |29745|  842K|    87   (2)|00:00:02|
| 3|    BITMAP OR                     |                 |     |      |            |        |
| 4|     BITMAP CONVERSION FROM ROWIDS|                 |     |      |            |        |
|*5|      INDEX RANGE SCAN            | IND_DOUBLE_NAME |     |      |    71   (0)|00:00:01|
| 6|     BITMAP CONVERSION FROM ROWIDS|                 |     |      |            |        |
|*7|      INDEX RANGE SCAN            | IND_DOUBLE_TYPE |     |      |    15   (0)|00:00:01|
--------------------------------------------------------------------------------------------
   5 - access("NAME"='T_DOUBLE_IND')
   7 - access("TYPE"='INDEX')

如果查询中经常对多列进行限制条件，且大部分情况只需要COUNT(*)查询，那么可能在各个列上建立单列索引会更适合。不过这种情况下，一般在数据仓库系统或报表系统中更加常见，而在这种类型的数据库中，一般直接就建立BITMAP索引了。

但是在OLTP系统中，BITMAP索引基本上是不可能的选择，因为这种类型的索引会极大的影响并发性，显然BTREE索引才是正确的选择。不过到底是单列索引还是复合索引，仍然没有一个确切的答案，还是要看具体的情况进行分析。

※当今的数据处理大致可以分成两大类：

OLTP:联机事务处理（on-line transaction processing）：OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。

OLAP:联机分析处理（on-line analytical processing）：LAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

=================================================================================================

简单介绍一下INDEX HASH JOIN执行计划

CREATE TABLE T_DOUBLE_IND 
    (ID NUMBER, 
    NAME VARCHAR2(30), 
    TYPE VARCHAR2(30), 
    CONTENTS VARCHAR2(4000));

CREATE INDEX IND_DOUBLE_NAME ON T_DOUBLE_IND (NAME);

CREATE INDEX IND_DOUBLE_TYPE ON T_DOUBLE_IND (TYPE);

现在建立了一个测试表和两个索引，下面看看如何利用索引哈希连接：

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')

SELECT NAME, TYPE FROM T_DOUBLE_IND WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
--------------------------------------------------------------------------------------------
| Id| Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                 |    1 |    32 |     3   (0)| 00:00:01 |
|*1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |    1 |    32 |     3   (0)| 00:00:01 |
|*2 |   INDEX RANGE SCAN          | IND_DOUBLE_NAME |    2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
   1 - filter("TYPE"='TABLE')
   2 - access("NAME"='T_DOUBLE_IND')

由于根据当前的列的统计情况分析，使用NAME列上的索引代价最小，因此Oracle选择了IND_DOUBLE_NAME索引扫描。

※ 通过INDEX_JOIN提示来选择INDEX HASH JOIN连接方式：

SELECT /*+ INDEX_JOIN(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |    32 |     6  (17)| 00:00:01 |
|*  1 |  VIEW              | index$_join$_001 |     1 |    32 |     6  (17)| 00:00:01 |
|*  2 |   HASH JOIN        |                  |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IND_DOUBLE_NAME  |     1 |    32 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| IND_DOUBLE_TYPE  |     1 |    32 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
   1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
   2 - access(ROWID=ROWID)
   3 - access("NAME"='T_DOUBLE_IND')
   4 - access("TYPE"='TABLE')

Oracle选择两个索引进行范围扫描，然后执行HASH JOIN，而HASH JOIN的连接列是ROWID，最后通过一个内部视图执行查询条件的过滤。从而避免对表进行访问。

不过这种执行计划的前提是，查询所选择的列必须能够通过索引完全的提供，如果包含了索引中不存在的列，Oracle还是会再次选择扫描表的:

SELECT /*+ INDEX_JOIN(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ID, NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND'AND TYPE = 'TABLE';

Execution Plan
--------------------------------------------------------------------------------------------
| Id| Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                 |    1 |    37 |     3   (0)| 00:00:01 |
|*1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |    1 |    37 |     3   (0)| 00:00:01 |
|*2 |   INDEX RANGE SCAN          | IND_DOUBLE_NAME |    2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
   1 - filter("TYPE"='TABLE')
   2 - access("NAME"='T_DOUBLE_IND')

不仅如此，就是选择了索引中包含的ROWID信息，Oracle仍然会不会选择INDEX HASH JOIN：

SELECT /*+ INDEX_JOIN(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ROWID, NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
--------------------------------------------------------------------------------------------
| Id| Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                 |    1 |    44 |     3   (0)| 00:00:01 |
|*1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |    1 |    44 |     3   (0)| 00:00:01 |
|*2 |   INDEX RANGE SCAN          | IND_DOUBLE_NAME |    2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
   1 - filter("TYPE"='TABLE')
   2 - access("NAME"='T_DOUBLE_IND')

其实这是没有道理的，因为索引中本身就包括ROWID信息，而且两个索引做HASH JOIN的时候，连接列就是ROWID，因此这个查询本来应该可以通过INDEX HASH JOIN来实现的，可能CBO优化器在处理这个算法的时候忽略了这个问题。

当然，查询COUNT(*)的操作也是可以利用INDEX HASH JOIN的：

SELECT /*+ INDEX_JOIN(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ COUNT(*) FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    32 |     6  (17)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                  |     1 |    32 |            |          |
|*  2 |   VIEW              | index$_join$_001 |     1 |    32 |     6  (17)| 00:00:01 |
|*  3 |    HASH JOIN        |                  |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| IND_DOUBLE_NAME  |     1 |    32 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN| IND_DOUBLE_TYPE  |     1 |    32 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
   2 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
   3 - access(ROWID=ROWID)
   4 - access("NAME"='T_DOUBLE_IND')
   5 - access("TYPE"='TABLE')

※Oracle会自动根据统计信息权衡是否选择INDEX HASH JOIN，而不需要通过HINT来强制执行：

UPDATE T_DOUBLE_IND SET NAME = 'T_DOUBLE_IND' WHERE ID <= 2000;
--2000 rows updated.

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')

SELECT NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |   259 |  8288 |    19   (6)| 00:00:01 |
|*  1 |  VIEW              | index$_join$_001 |   259 |  8288 |    19   (6)| 00:00:01 |
|*  2 |   HASH JOIN        |                  |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IND_DOUBLE_NAME  |   259 |  8288 |     5   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| IND_DOUBLE_TYPE  |   259 |  8288 |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
   1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
   2 - access(ROWID=ROWID)
   3 - access("NAME"='T_DOUBLE_IND')
   4 - access("TYPE"='TABLE')

但是由于INDEX HASH JOIN的限制，使得这个查询只能满足查询索引列或COUNT(*)的情况，因此使得这个执行计划的使用机会大大降低。

※而如果建立复合索引，讲会使得查询代价大为降低，而且可以满足继续扫描表的查询要求：

CREATE INDEX IND_DOU_NAM_TYP ON T_DOUBLE_IND (NAME, TYPE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND');

只查询索引上字段的情况

SELECT NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |   240 |  7680 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_DOU_NAM_TYP |   240 |  7680 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
   1 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')

查询索引上没有的字段情况

SELECT ID, NAME, TYPE FROM T_DOUBLE_IND A WHERE NAME = 'T_DOUBLE_IND' AND TYPE = 'TABLE';

Execution Plan
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                 |  240 |  8880 |   149   (0)| 00:00:03 |
| 1 |  TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND    |  240 |  8880 |   149   (0)| 00:00:03 |
|*2 |   INDEX RANGE SCAN          | IND_DOU_NAM_TYP |  240 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
   2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')

可见复合索引执行效率很高

=================================================================================================

总结

同时访问两个以上索引的三种情况而言，AND-EQUAL执行计划在Oracle已经不在推荐，连AND-EQUAL这个HINT在文档中也不再介绍，只是为了后向兼容性而继续保留。原因已经介绍过，Oracle无法通过统计信息来判断这种执行路径的代价。

而INDEX HASH JOIN则由于限制条件的问题很难广泛使用。

唯一对于多个单列索引而言的优势在于BITMAP AND/OR操作，而一般这种执行计划在数据仓库类型的查询中更为常见。

因此，对于要求快速得到查询结果的OLTP系统中，复合索引应该是更为合理的选择，不要希望通过建立多个单列索引，来完全代替复合索引的创建。

最后，什么索引最适合你的系统仍然要具体情况具体分析，实践才是检验索引的唯一真理。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。