Oracle学习之：索引

一、B-树索引

简述：

B-树索引实现类似于倒置的树型结构，包括根节点、分支节点和叶子节点，并且使用树遍历算法来搜索列值。叶子节点中包含一对（值、行编号）值，值对应于索引键列，行编号则表示行在表数据块中的物理位置。分支节点包含叶子节点目录以及存储在其中的叶子节点的值范围。根节点包含分支节点目录以及这些分支节点所包括的值范围。

下图是一个数值类型列的B-树索引结构。为了便于理解，这张图对索引结构进行了概括，实际的索引结构要复杂的多。索引的根节点保存分支节点地址及分支块中所访问值的范围。分支节点保存叶子节点地址及叶子块中的值范围。

如果sql语句使用谓语n1=12000来查找一个列值12000，将会从根节点开始进行索引范围扫描，遍历到第2个分支节点，因为第2个分支节点保存的值范围为11001到22000。
然后遍历第4个叶子节点，找到对应的行编号使用这个行编号从表中访问到数据行。行编号是表数据块中数据行物理位置的指针。

创建方式：

create index <index_name> on <table_name>(column_names);

适用范围：

1、 主键、唯一性约束
2、重复度非常低的字段
3、一定条件下不适用重复度非常高的字段

二、位图索引

简述：

顾名思义，与“位”有关。大家都知道，计算机中的所有信息最终都是通过“位bit”来运算的， 二进制位运算在计算机中是非常高效的。每一个二进制位都可以取值0或者1，而取值的确切含义是由具体的上下文环境决定的。在oracle位图索引中，每一个二进制位代表了某一行中索引列的取值情况。例如，学生表中性别列的位图索引结构如下：
男:0101001101
女:1010110010
在上面的位图结构中，存储了10条学生记录的性别分布情况，以“男”性别为例，从左到右的第n个二进制位代表了第n条记录是否性别为男，如果二进制位为1，代表true即性别为男，0代表false即性别不为男。以此类推，从图中可以看出，第一条记录的性别为女，第二条记录的性别为男，…第九条记录的性别为女，第十条记录的性别为男。

创建方式：

create bitmap index <index_name> on <table_name>(column_name);

适用范围：

1、 适用较少唯一值的列进行只读运算
2、适用OR\AND这类逻辑操作
3、不适应于大量更新的列或较多DML（增、删、改）操作的表

三、索引组织表

创建方式：

create table hb_product(...)  organization index ;

四、函数索引

创建方式：

create index ind_char_xx on table (to_char(xx));

五、反向索引

创建方式：

create index ind_rev_xx on table(xx) reverse;

等等还有其他一些索引，这里不在体现。

六、看看索引失效情况：

1、联合索引引导列后面的的字段组合走index skip scan（此索引有的版本不支持）
2、B-tree索引中单独使用索引列is null不走索引（is not null走索引 index full scan），联合索引中is null走索引（位图索引 is null,is not null 都会走）
3、B-tree索引中索引列上使用函数或计算索引失效（可创建函数索引）
4、索引列上使用 ！= 、 <> 索引失效
5、索引列上使用not in（可以使用not exists替代not in）
6、使用like ‘%a’ 开头不走索引

OR会引起索引失效的说法是这样来的：

如果是这样一个查询

SELECT * FROM TB WHERE A=1 AND B>2 AND C<3 AND D IN (4,5,6)

并且在TB表上有这样一个索引：

CREATE INDEX INX_TB_ABCD ON TB (A,B,C,D)

那么这个查询可以用到这个索引
如果同样是这个索引，查询换成

SELECT * FROM TB_ WHERE A=1 OR B>2 OR C<3 OR D IN (4,5,6)

那么这个查询就用不到上面那个索引，因为结果集是几个条件的并集，最多只能在查找A=1的数据时用索引，其它几个条件都需要表扫描，那优化器就会选择直接走一遍表扫描，所以索引就失效了。

那么像第二个查询这样的应该怎么建索引呢，答案就是四个列上各建一个索引，或者只在选择性最高的列上建索引，比如A=1的数据量很少，就在A上建，如果D是4，5，6的数据很少，就在D上建，这样优化器就会选择先走索引查找，再对找出的结果集进行筛选，扫描数就会大幅减少。

七、以下索引发生条件不同版本有不同的结果，需要实战

• index range scan 索引范围扫描
• index full scan 索引全扫描
• index skip scan 索引跳跃扫描
• index unique scan 索引唯一性扫描