oracle学习笔记-索引-(10)

      创建索引的原则：

      >如果每次查询仅选择少量的行，应该创建索引

      >如果在表上需要进行频繁的DML操作，不应该创建索引

      >尽量不要在有很多重复值的列上建立索引

      >不要在太小的表上建立索引

 

      创建索引的方式：

      1)     在表上指定主键约束和唯一性约束时自动创建索引

      2)     通过命令手工创建

 

1、 B*树索引

        B*树索引是最常用的索引形式，其实现类似于二叉查找树，B*树索引中所有的叶子节点都在同一层，无论你查找哪一条数据，需要执行的I/O数据是一样的。在一个表中，列上的数据越随机，就越能体现B*树索引的优越性。

        B*树索引的理论结构如下图：

        叶子节点是双向链表的结构，便于查找区间数据。

创建语法如下：

SQL>CREATEINDEX test_idx1 ON test(COL)

2、 反向索引

        当列中的数据是有序时，随着数据的插入，使用B*数索引会导致树的倾斜，索引树的层级增加很快，而索引树引起的IO次数和索引的层级成正比，此时使用B*树索引就没什么意义了。反向索引将索引列的值按照相反顺序存储在索引中，从而将有规律的分布转化为无规律的分布，然后按照转化后的数据创建一个B*树索引。

        通常，使用数据时（常见于批量插入操作）都比较集中在一个连续的数据范围内，那么在使用正常的索引时就很容易发生索引叶子块过热的现象，严重时将会导致系统性能下降。使用反向索引可以减少热点块，提高系统性能。

        但是在反向索引中不能使用范围扫描(如，between，>，<)读取数据，此时将进行全表扫描。

创建语法如下：

SQL>CREATEINDEX test_idx2 ON test(COL) REVERSE;

3、 位图索引

        对于某列中重复值很多，此时更适合创建位图索引。在位图索引中，为索引列上每个不同的值分配一个位图，这个不同的值称为键值。表中的每行数据在位图中对应一个二进制位，如果该行中索引列的值与键值相同，那么对应二进制位为1，否则为0。

位图索引的逻辑结构图如下：

        此时如果需要查询列值为“学士”的列，首先在查找键值为“学士”的位图，再在位图中查找所有与二进制1对应的列。

创建位图索引如下：

SQL>CREATEBITMAP INDEX test_idx3 ON test(COL)。

使用位图索引需要使用INDEX_COMBINE提示指定：

SQL>SELECT/*+ index_combine(test COL) */ COL2 FROM test WHERE COL=’…’;

4、 基于函数的索引

对于下面的查询：

SQL>SELECT * FROM emp WHERE lower(ename)=’smith’;

        即使在ename列上创建了索引，由于对ename列进行了操作，所以会导致全表扫描。此时可以利用基于函数的索引来解决这类问题。基于函数的索引建立在某个函数或者某个表达式的基础上。创建方式如下：

SQL>CREATEINDEX emp_idx ON emp(lower(ename));

索引所基于的函数可以是预定义的，也可以是用户定义的函数。基于函数的索引创建之后，需要对表进行分析之后才能使用。

SQL>ANALYZETABLE emp COMPUTE STASTICS;

5、 分区索引

在分区表上可以创建全局索引或者分区索引。

全局索引创建方式如下：

SQL>CREATE INDEX test_idx4 ON test(COL) GLOBAL;

分区索引创建方式：

SQL>CREATE INDEX test_idx4 ON test(COL) LOCAL;

6、 索引的维护

1)     回收索引中未使用的空间

SQL>ALTER INDEX index_name DEALLOCATE UNUSED;

2)     重建索引可以整理由于DML操作产生的碎片：

SQL>ALTER INDEX index_name REBUILD;

当表上有用户在执行操作时，可在线重建索引：

SQL>ALTER INDEX index_name REBUILD ONLINE；

3)     如果相邻空间中有空闲空间，可以将这些数据块中的索引项合并在一个数据块中：

SQL>ALTER INDEX index_name COALESCE;