查询访问同一表的两个以上索引（二）

 

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

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

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

 

首先建立一个测试表：

SQL> DROP TABLE T_DOUBLE_IND PURGE;

Table dropped.

SQL> CREATE TABLE T_DOUBLE_IND

  2  (ID NUMBER,

  3  NAME VARCHAR2(30),

  4  TYPE VARCHAR2(30),

  5  CONTENTS VARCHAR2(4000));

Table created.

SQL> INSERT INTO T_DOUBLE_IND

  2  SELECT ROWNUM,

  3  OBJECT_NAME,

  4  OBJECT_TYPE,

  5  LPAD('A', 1000, 'A')

  6  FROM DBA_OBJECTS;

75856 rows created.

SQL> CREATE INDEX IND_DOUBLE_NAME

  2  ON T_DOUBLE_IND (NAME);

Index created.

SQL> CREATE INDEX IND_DOUBLE_TYPE

  2  ON T_DOUBLE_IND (TYPE);

Index created.

现在建立了一个测试表和两个索引，下面看看如何使用BITMAP索引的AND操作：

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')

PL/SQL procedure successfully completed.

SQL> SET AUTOT ON EXP

SQL> SELECT ID, NAME, TYPE

  2  FROM T_DOUBLE_IND

  3  WHERE NAME = 'T_DOUBLE_IND'

  4  AND TYPE = 'TABLE';

        ID NAME                           TYPE

---------- ------------------------------ ------------------------------

     75737 T_DOUBLE_IND                   TABLE

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3887138334

--------------------------------------------------------------------------------------------

| 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 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("TYPE"='TABLE')

   2 - access("NAME"='T_DOUBLE_IND')

SQL> SELECT /*+ INDEX_COMBINE(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ID, NAME, TYPE

  2  FROM T_DOUBLE_IND A

  3  WHERE NAME = 'T_DOUBLE_IND'

  4  AND TYPE = 'TABLE';

        ID NAME                           TYPE

---------- ------------------------------ ------------------------------

     75737 T_DOUBLE_IND                   TABLE

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2381509496

--------------------------------------------------------------------------------------------

|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 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("NAME"='T_DOUBLE_IND')

   7 - access("TYPE"='TABLE')

BITMAP索引的AND操作只有在CBO模式下才会启用，因此收集表的统计信息。由于根据当前的列的统计情况分析，使用NAME列上的索引代价最小，因此Oracle选择了IND_DOUBLE_NAME索引扫描。

可以通过INDEX_COMBINE提示来指定索引进行BITMAP AND/OR操作，这种情况下，Oracle将两个BTREE索引首先转化为BITMAP索引，然后执行BITMAP AND或BITMAP OR的操作，根据需要决定是否再将BITMAP索引转化回BTREE索引，然后根据ROWID访问表得到最终的结果。

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

SQL> SET AUTOT OFF

SQL> UPDATE T_DOUBLE_IND

  2  SET NAME = 'T_DOUBLE_IND'

  3  WHERE TYPE = 'SYNONYM';

22600 rows updated.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')

PL/SQL procedure successfully completed.

SQL> SET AUTOT ON EXP

SQL> SELECT ID, NAME, TYPE

  2  FROM T_DOUBLE_IND

  3  WHERE NAME = 'T_DOUBLE_IND'

  4  AND TYPE = 'TABLE';

        ID NAME                           TYPE

---------- ------------------------------ ------------------------------

     75737 T_DOUBLE_IND                   TABLE

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2581345372

--------------------------------------------------------------------------------------------

|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 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("TYPE"='TABLE')

   7 - access("NAME"='T_DOUBLE_IND')

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

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

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

SQL> SELECT ID, NAME, TYPE

  2  FROM T_DOUBLE_IND

  3  WHERE NAME = 'T'

  4  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

----------------------------------------------------------

Plan hash value: 1253459974

--------------------------------------------------------------------------------------------

|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 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("NAME"='T')

   7 - access("TYPE"='CONTEXT')

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

SQL> SET AUTOT TRACE EXP

SQL> SELECT ID, NAME, TYPE

  2  FROM T_DOUBLE_IND

  3  WHERE NAME = 'T_DOUBLE_IND'

  4  OR TYPE = 'INDEX';

Execution Plan

----------------------------------------------------------

Plan hash value: 4134729579

----------------------------------------------------------------------------------

| 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 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("NAME"='T_DOUBLE_IND' OR "TYPE"='INDEX')

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

SQL> SET AUTOT ON EXP

SQL> SELECT COUNT(*)

  2  FROM T_DOUBLE_IND

  3  WHERE NAME = 'T_DOUBLE_IND'

  4  OR TYPE = 'INDEX';

  COUNT(*)

----------

     33058

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4098198664

--------------------------------------------------------------------------------------------

|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|

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("NAME"='T_DOUBLE_IND')

   7 - access("TYPE"='INDEX')

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

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