oracle 索引理解,技术感悟：我对Oracle索引的理解

在这篇文章里，给大家简单介绍一下本人对Oracle索引的理解，如有不妥的地方，请不吝指教。

本文只讲最最平常最最简单的索引，就是以create index ix on tx(a,b,c);形式创建的索引，而不讲位图索引、反向键索引、倒序索引、基于函数的索引等等。其实呢，只要是基于B树的索引，不管是在Oracle, Mysql，还是其它数据库中，原理应当都是一样的。

索引最重要的一个性质应该就是有序，索引中的每一项，是从左到右，从小到大，以严格的顺序排列好的。

下面的讨论都以上面的索引ix(a,b,c)为例。

把这棵索引的叶子节点画到纸上，大概是这样的：

a1 a2 a3 ...... an

b1 b2 b3 ...... bn

c1 c2 c3 ...... cn

上面这个3×n的矩阵，每一列代表了一条记录，同时这一列记录，也对应了表里的唯一一条记录。当然，在Oracle里，对于non-unique索引，需要补上rowid，才是真正唯一的。上面的索引相当于create unique index ix on tx(a,b,c,rowid); 我们把这个细节忽略掉。

把每一列看作一个向量，vi = (ai, bi, ci)，

有序的含义就是：

vi < vj iff i < j;

vi < vj这么定义：

(ai < aj) or (ai = aj and bi < bj) or (ai = aj and bi = bj and ci < cj)

从这个基本性质，我们可以得到一些其它性质(为了打字方便，ai+k表示a(i+k),而不是a(i)+k)：

1) 如果ai, ai+1, ……, ai+k 都是相等的，那么，

bi <= bi+1 <= …… <= bi+k

2) 如果ai, ai+1, ……, ai+k是相等的，而且bi,bi+1, ……, bi+k也是相等的，那么

ci <= ci+1 <= …… < ci+k

但是从 ai, ai+1, ……, ai+k相等，我们得不到

ci <= ci+1 <= …… <= ci+k这个结论。

索引相关的很多问题，都和上面提到的这几个性质有关系。

下面来看几个常见的查询:

q1) select * from tx where a = :va and b = :vb;

q2) select * from tx where b = :vb and c = :vc;

q3) select * from tx where a = :va and c = :vc;

q4) select * from tx where a = :va order by b;

q5) select * from tx where a = :va order by b, c;

q6) select * from tx where a = :va order by c;

q7) select * from tx where a = :va order by b, c desc;

q8) select * from tx where a = :va order by b desc, c desc;

q9) select * from tx where a = :va and b <= :vb1

qa) select * from tx where a = :va and b >= :vb

qb) select * from tx where a = :va and c >= :vc

qc) select * from tx where a = :va and b >= :vb order by c

大家可以考虑一下这些查询各自会以怎样的方式执行，不同查询之间有什么区别？

同样，为什么在索引字段上作了函数运算之后，索引不可用？

考虑下面这个语句：

select * from tx where f(a) = :vfa;

首先，在字段 a上作了函数运算之后，排序的规则是否仍旧一样? a < b 与 f(a) < f(b)是否等价？

其次，就算f(a)和a的排序规则一样，但是索引块中存的a， 但是你传给它的是经过了函数运算的值：vfa，只有oracle知道函数f的反函数inv_f，并在vfa上做inv_f(:vfa)计算之后，才能通过索引的B树结果进行查找。

当然，现实中f可能不是显示的，而是隐式的，如传入参数和字段类型不匹配的情况下，Oracle可能在字段上作函数运算。从语句上可能看不出索引字段上被做了函数运算，但Oracle内部已经在字段上运用了函数。这样也会导致索引不可用，这种情况下用hint强制使用索引也是没用的。

通过dbms_xplan.display_cursor可以或许可以查看到这种隐式类型转换；

通过v$sql_bind_metadata应当可以查看到每个绑定变量的类型；

通过v$sql_bind_capture这个视图甚至可以看到每个绑定变量具体的值，不要把bind_capture和bind peek搞混哦，而且这里bind_cature也不会每绑定一次变量就capture一次，不然对执行量非常高，绑定频繁的语句，capture以同样频率进行的话，开销可能还是有点大的。

21/212>