Oracle索引

素颜马尾好姑娘i 2021-09-15 08:36 356阅读 0赞

用户可以在Oracel中创建多种类型的索引，以适应各种表的特点。按照索引数据的存储方式可以将索引分为B树索引、位图索引、反向键索引和基于函数的索引；按照索引列的唯一性可以分为唯一索引和非唯一索引；按照索引列的个数可以分为单列索引和复合索引。

**建立主键索引和唯一索引的主要目的除了数据的完整性与一致性之外，还具有提高查询速度的作用。**

建立和规划索引时，必须选择合适的表和列，如果选择的表和列不合适，不仅无法提高查询速度，反而会极大的降低DML操作的速度，所以建立索引必须注意一下几点：

（1）索引应该建立在WHERE子句频繁引用表列上，如果在大表上频繁使用某列或某几列作为条件执行索引操作，并且检索行数低于总行数15%，那么应该考虑在这些列上建立索引。

（2）如果经常需要基于某列或某几列执行排序操作，那么在这些列上建立索引可以加快数据排序速度。

（3）限制表的索引个数。索引主要用于加快查询速度，但会降低DML操作的速度。索引越多，DML操作速度越慢，尤其会极大的影响INSERT和DELETE操作的速度。因此，规划索引时，必须仔细权衡查询和DML的需求。

（4）指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块上预留部分空间，该预留空间是为将来的INSERT操作准备的。如果将来在表上执行大量的INSERT操作，那么应该在建立索引时设置较大的PCTFREE。

（5）将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提高访问性能。

（6）当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。

（7）不要在小表上建立索引。

（8）为了提高多表连接的性能，应该在连接列上建立索引。

# 1、创建索引 #

在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。建立索引可以使用CREATE INDEX语句。

**【实例】**在SCOTT模式下，为EMP表的DEPTNO列创建索引（B树索引），代码如下：

CREATE INDEX EMP_DEPTNO_INDEX ON EMP(DEPTNO);

# 2、修改索引 #

修改索引时通常使用ALTER INDEX语句来完成。

为表建立索引后，随着对表不断进行更新、插入和删除操作，索引中会产生越来越多的存储碎片，这将对索引的工作效率产生负面影响。这是可以采取两种方式来清除碎片——重建索引或合并索引。合并索引只是将B树索引中叶子节点的存储碎片合并在一起，并不会改变索引的物理组织结构。

**【实例】**在SCOTT模式下，对索引EMP\_DEPTNO\_INDEX执行合并操作，代码如下：