经常看到有人提出这样的疑问，我在同一张表上建立了多个索引，为什么Oracle每次都选择一个，而不能同时利用多个索引呢。一般来说，常见的访问同一张表的两个以上索引，存在三种情况，AND-EQUAL、INDEX HASH JOIN和BITMAP INDEX AND/OR。
此外，还有一个设计上的疑问，如果有A、B、C三个字段，都可能作为查询条件，是建立多个复合索引好，还是建立三个单列的索引。这个问题之所以不好回答是因为和业务或者说和查询的模式有很大的关系，不过如果理解了Oracle什么时候会选择一个以上的索引来访问表，就会对于理解如何设计合理的索引有很大的帮助。
简单介绍一下AND-EQUAL执行计划。
首先建立一个测试表：
SQL> CREATE TABLE T_DOUBLE_IND
E g;B-co.~A z0 2 (ID NUMBER,ITPUB个人空间5Iu,D | {c7c
3 NAME VARCHAR2(30),ITPUB个人空间)`P4aWnw6| A|
4 TYPE VARCHAR2(30),
S#? P lp `k0 5 CONTENTS VARCHAR2(4000));
Table created.
SQL> INSERT INTO T_DOUBLE_INDITPUB个人空间\ d9B7h(JK
2 SELECT ROWNUM,
&y yt a+[:Ei.Q0 3 OBJECT_NAME,
K]k!@N7PV0 4 OBJECT_TYPE,ITPUB个人空间~TP nL`
5 LPAD('A', 1000, 'A')ITPUB个人空间{Wf8D&tU
6 FROM DBA_OBJECTS;
75856 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE INDEX IND_DOUBLE_NAME
6?(q`9f7c0 2 ON T_DOUBLE_IND (NAME);
Index created.
SQL> CREATE INDEX IND_DOUBLE_TYPEITPUB个人空间1Dk+f^i-c p$Gr
2 ON T_DOUBLE_IND (TYPE);
Index created.
现在建立了一个测试表和两个索引，下面首先来看看AND-EQUAL执行方式：
SQL> SELECT ID, NAME, TYPEITPUB个人空间-K$GrB c,r P
2 FROM T_DOUBLE_IND
/~5| gb6] wN0 3 WHERE NAME = 'T_DOUBLE_IND'ITPUB个人空间T.H#R;uE2x+d!} `~
4 AND TYPE = 'TABLE';
ID NAME TYPE
r(|#W4o/u_I s@0---------- ------------------------------ ------------------------------
(a,j|S'Wl _DQ_0 75688 T_DOUBLE_IND TABLE
ITPUB个人空间']WZ m Kem
Execution PlanITPUB个人空间"Y'Q3@%d,n.\ }-K
----------------------------------------------------------
Ai2ph+T{RK0Plan hash value: 474554719
-------------------------------------------------------
K`9c,` O |s0| Id | Operation | Name |ITPUB个人空间,q7Bc/e.Lfx3M(S!k
-------------------------------------------------------
zGU+k@f+X5X p0| 0 | SELECT STATEMENT | |
F8rNQVu4T(D]0| 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND |ITPUB个人空间;H)j;Q]@
| 2 | AND-EQUAL | |
C"T'UII1tRT0|* 3 | INDEX RANGE SCAN | IND_DOUBLE_NAME |ITPUB个人空间h"iy6A!A,ZF&J
|* 4 | INDEX RANGE SCAN | IND_DOUBLE_TYPE |ITPUB个人空间S)e xz/T)?;O{"_r:Y
-------------------------------------------------------
Predicate Information (identified by operation id):ITPUB个人空间`pdN-?
---------------------------------------------------
3 - access("NAME"='T_DOUBLE_IND')ITPUB个人空间 A3~"G2h3On
4 - access("TYPE"='TABLE')
NoteITPUB个人空间R;k By?_sk ^N
-----ITPUB个人空间6\)Rn/yK
- rule based optimizer used (consider using cbo)
由于指定了两个列，且两个列上都包含索引，Oracle选择了扫描两个索引，并使用了AND-EQUAL执行计划。这种扫描方式是分别通过两个索引获取索引键值对应的ROWID，然后合并两个扫描中相等的ROWID，并通过这个ROWID来扫描表。
不过观察执行计划可以发现，当前的优化模式是RBO，如果收集一下索引，再次运行同样的查询：
SQL> SHOW PARAMETER OPTIMIZER_MODE
NAME TYPE VALUE
Pw R4jR CTV0------------------------------------ ----------- ------------------------------ITPUB个人空间 wf[r_O*]"w*I
optimizer_mode string CHOOSEITPUB个人空间A)?F)k1q'Mx1a SQ
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
PL/SQL procedure successfully completed.
SQL> SELECT ID, NAME, TYPEITPUB个人空间$~A.T| C!o3Dhw}U
2 FROM T_DOUBLE_IND
q0I7Mgn$}"LL,mX0 3 WHERE NAME = 'T_DOUBLE_IND'
&\;~!_\Q;^+{ Gy,f0 4 AND TYPE = 'TABLE';
ID NAME TYPEITPUB个人空间,~uB c0?2y/ao7k
---------- ------------------------------ ------------------------------
%ai^ m j!ax9D7l"lm({0 75688 T_DOUBLE_IND TABLE
@G'K5r0e N g]yM0Execution Plan
d/U { CC0_0----------------------------------------------------------
L:c%_:n TT0Plan hash value: 3887138334
--------------------------------------------------------------------------------------------ITPUB个人空间 eO^e"rCM T:?,S W
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
/MZ\P,y:s*{u#d'@0--------------------------------------------------------------------------------------------
Up*RR4ypR0| 0| SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |ITPUB个人空间3E;l:O [+{%b
|* 1| TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 37 | 3 (0)| 00:00:01 |
$KY ov;t` l0|* 2| INDEX RANGE SCAN | IND_DOUBLE_NAME | 2 | | 1 (0)| 00:00:01 |ITPUB个人空间*M K6^/jzIFU
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):ITPUB个人空间)H6RL Ulzy&i
---------------------------------------------------
1 - filter("TYPE"='TABLE')
%K{u8FP~0 2 - access("NAME"='T_DOUBLE_IND')
可以看到，收集统计信息后，优化模式变为CBO，但是Oracle只选择了一个索引进行扫描。对于当前的情况，NAME列的选择度非常高，因此这种方式的代价最低。
SQL> SELECT /*+ INDEX(A IND_DOUBLE_NAME) INDEX(A IND_DOUBLE_TYPE) */
B L]O!_0 2 ID, NAME, TYPEITPUB个人空间0X }d d r
3 FROM T_DOUBLE_INDITPUB个人空间4L8m5TGU uH ~
4 WHERE NAME = 'T_DOUBLE_IND'ITPUB个人空间(z+v6Z2ak-Wy
5 AND TYPE = 'TABLE';
ID NAME TYPEITPUB个人空间 q s C0e jt wv'}q
---------- ------------------------------ ------------------------------ITPUB个人空间|D2r5O#[ mjV!D
75689 T_DOUBLE_IND TABLE
ITPUB个人空间T;bg3a:@ fJ"Z$c`
Execution PlanITPUB个人空间e,vPwt[uP
----------------------------------------------------------ITPUB个人空间kp*W:EqG-o-d
Plan hash value: 3887138334
--------------------------------------------------------------------------------------------
n7TD Tw$_#w"_3g0| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |ITPUB个人空间pB,D6G{8_5ACnL
--------------------------------------------------------------------------------------------ITPUB个人空间}Y h&Y1m{(R)c
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |ITPUB个人空间 Qk`o9O)Y5c e3rs s;@l
|*1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 37 | 3 (0)| 00:00:01 |ITPUB个人空间X*o1v/U,j ` rm
|*2 | INDEX RANGE SCAN | IND_DOUBLE_NAME | 2 | | 1 (0)| 00:00:01 |
8_2m sk1N nv"?0--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
M2PD sLB@0---------------------------------------------------
1 - filter("TYPE"='TABLE')ITPUB个人空间)nt4Yr V opw
2 - access("NAME"='T_DOUBLE_IND')
即使通过HINT指定两个索引，CBO优化器也只是会选择其中一个索引来进行扫描，而自动忽略另一个选择度低的索引。
只有使用AND_EQUAL提示，才能在CBO的情况下使用AND-EQUAL执行计划：
SQL> SELECT /*+ AND_EQUAL(A IND_DOUBLE_NAME IND_DOUBLE_TYPE) */ ID, NAME, TYPE
'} _*Ee }0 2 FROM T_DOUBLE_IND A
h"t(N"Js||0 3 WHERE NAME = 'T_DOUBLE_IND'
M3i4j(zg&M/^ WrF0a0 4 AND TYPE = 'TABLE';
Execution Plan
x"o3KTvH9d AX0----------------------------------------------------------
0v I} s Zer[0Plan hash value: 474554719
--------------------------------------------------------------------------------------------ITPUB个人空间TD x?l3L6@a/|
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |ITPUB个人空间!L I IY;k(P[
--------------------------------------------------------------------------------------------ITPUB个人空间 RgP3o KG*g n
| 0 | SELECT STATEMENT | | 1 | 37 | 21 (0)| 00:00:01 |ITPUB个人空间.BmW%V|
|*1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 1 | 37 | 21 (0)| 00:00:01 |
h3hX\G?*zI.`7W0| 2 | AND-EQUAL | | | | | |ITPUB个人空间.o!aVY X-YH3E2o%W
|*3 | INDEX RANGE SCAN | IND_DOUBLE_NAME | 2 | | 1 (0)| 00:00:01 |
&Pt4z0N;u T0|*4 | INDEX RANGE SCAN | IND_DOUBLE_TYPE | 9286 | | 20 (0)| 00:00:01 |ITPUB个人空间Ix B.l#D]
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):ITPUB个人空间 tW;e+C'q j)S:X
---------------------------------------------------
1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')ITPUB个人空间f)Av K|/J
3 - access("NAME"='T_DOUBLE_IND')
T ]N'eH3P8b0 4 - access("TYPE"='TABLE')
在CBO下，Oracle不会自动选择AND-EQUAL执行计划，这是因为Oracle可以根据直方图来判断一个列上各个值的选择度，但是从统计信息无法获得两个不同的列合AND-EQUAL之后的选择度，这个操作后，如果得到的ROWID记录很少，那么这个查询的效率就会很高，如果AND-EQUAL后得到大量的ROWID，那么查询的效率就会很差，这时应该选择全表扫描或其他的执行计划。由于CBO都是根据统计信息分析得到的结果，而AND-EQUAL的结果对于CBO是未知的，因此CBO不会选择AND-EQAUL这种扫描方式。
如果要Oracle获得这种统计信息，最简单的方法就是建立一个复合索引，Oracle在分析索引列的时候自动会分析两个列的组合情况，从而在查询的时候可以准确的判断是否应该使用这个复合索引。
SQL> SET AUTOT OFF
w4A9R;b$we,Ejf0SQL> CREATE INDEX IND_DOU_NAM_TYP
[L~;i^C3J3r] wV0 2 ON T_DOUBLE_INDITPUB个人空间]o bL \R
3 (NAME, TYPE);
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_DOUBLE_IND')
PL/SQL procedure successfully completed.
SQL> SET AUTOT TRACE EXPITPUB个人空间#r D/N(u6|G gD-x8t
SQL> SELECT ID, NAME, TYPEITPUB个人空间4w}1e9D/SBm
2 FROM T_DOUBLE_INDITPUB个人空间R ^_Rc(}9v?h _
3 WHERE NAME = 'T_DOUBLE_IND'
/`F U,V'FiS'j0 4 AND TYPE = 'TABLE';
Execution Plan
L i"] VX0----------------------------------------------------------
f rF;l e6x v"F;L P0Plan hash value: 1808990274
--------------------------------------------------------------------------------------------
n0i r8zR O0| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |ITPUB个人空间+RC4To4koN+C3Tc
--------------------------------------------------------------------------------------------ITPUB个人空间u W^-T*^lsg S
| 0 | SELECT STATEMENT | | 2 | 74 | 3 (0)| 00:00:01 |ITPUB个人空间t~C%YcZa
| 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 2 | 74 | 3 (0)| 00:00:01 |ITPUB个人空间0X-g v8i |8y4_Mg
|*2 | INDEX RANGE SCAN | IND_DOU_NAM_TYP | 2 | | 1 (0)| 00:00:01 |
z x8A$[ ?0--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
@1y${W8a F0---------------------------------------------------
2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
SQL> SET AUTOT OFF
/xV v$o| I9x$C0SQL> UPDATE T_DOUBLE_INDITPUB个人空间$X o1S z @Y;ax,eu
2 SET NAME = 'T_DOUBLE_IND'ITPUB个人空间 C3m5{#v"].W,~
3 WHERE ID <= 20000;
20000 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 TRACE EXP
T U4J NN4\4KXM0SQL> SELECT ID, NAME, TYPE
0Y-s#HWr0 2 FROM T_DOUBLE_IND
*ho}\%j9a0 3 WHERE NAME = 'T_DOUBLE_IND'ITPUB个人空间l*vl} C%f
4 AND TYPE = 'TABLE';
Execution PlanITPUB个人空间)G%a Ar9S|K a I({
----------------------------------------------------------
!x y n5|/z/A f;fe0Plan hash value: 1808990274
--------------------------------------------------------------------------------------------
(\ L u@ ir0| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |ITPUB个人空间f7W \WI\Ql+` L
--------------------------------------------------------------------------------------------ITPUB个人空间)YQ3j#aY(`tU*L
| 0 | SELECT STATEMENT | | 2277 | 77418 | 1225 (1)| 00:00:18 |
W b nzU;l^$\8H0| 1 | TABLE ACCESS BY INDEX ROWID| T_DOUBLE_IND | 2277 | 77418 | 1225 (1)| 00:00:18 |ITPUB个人空间7N8nb']P&Q7L
|*2 | INDEX RANGE SCAN | IND_DOU_NAM_TYP | 2277 | | 11 (0)| 00:00:01 |ITPUB个人空间 Ei C @&\$B@*x6uEQ
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):ITPUB个人空间pv,a)Rb(^MC
---------------------------------------------------
2 - access("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
SQL> SET AUTOT OFF
T.?)^'{W.G8k,Y6u0SQL> UPDATE T_DOUBLE_INDITPUB个人空间]4` A#_1E{d!S |"Q c
2 SET TYPE = 'TABLE'ITPUB个人空间B A.e m,[!V!N
3 WHERE ID <= 20000;
20000 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 TRACE EXP
;D/g-R \^ Q pa0TK q0SQL> SELECT ID, NAME, TYPE
k D3Tzg z^'O0 2 FROM T_DOUBLE_IND
x8NPe(B0~5p:p(g5`0 3 WHERE NAME = 'T_DOUBLE_IND'
v Hx h|$n/_0 4 AND TYPE = 'TABLE';
Execution Plan
G6sMZl@ R0----------------------------------------------------------
n?'k ^ ?+ZT2H c0Plan hash value: 4134729579
----------------------------------------------------------------------------------ITPUB个人空间(O5M'y P x|-[A
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |ITPUB个人空间+H P+R5j8}dd*[
----------------------------------------------------------------------------------
1iN.Q1Y b:z@0| 0 | SELECT STATEMENT | | 7778 | 258K| 2010 (1)| 00:00:29 |
/S3y$I)uLO{c7Z/Rh0|* 1 | TABLE ACCESS FULL| T_DOUBLE_IND | 7778 | 258K| 2010 (1)| 00:00:29 |ITPUB个人空间 _az4g8t|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
? Ubia F0---------------------------------------------------
1 - filter("NAME"='T_DOUBLE_IND' AND "TYPE"='TABLE')
在建立复合索引后，对于刚才的查询，Oracle会自动选择复合索引。即使将表中的20000条记录全部更新为NAME=’T_DOUBLE_BIN’，由于TYPE列的选项条件，在加上索引相对于表来说要窄得多，Oracle仍然选择了复合索引扫描。
如果将TYPE列也更新20000条记录，使得NAME=’T_DOUBLE_IND’和TYPE=’TABLE’的选择性很差，这时Oracle就会自动选择全表扫描来代替索引扫描。
