Oracle PL/SQL开发基础（第四弹：索引）

索引是数据库管理系统提供的一种用来快速访问表中数据的机制。在数据库管理系统系统中，索引的意义非常重大，使用索引可以显著提高对数据库数据的查询效率，减少磁盘的IO操作，提升整个数据库系统的性能。当定义主键或唯一性的约束时，Oracle会自动在相应的字段上创建唯一性索引。

索引简介

索引是建立在数据库表中的一列或多列用来加速访问表中互数据的辅助对象。

索引有以下优点：
- 索引可以大大加快检索数据的速度。
- 使用唯一性索引可以保证数据库表中每一行数据的唯一性。
- 通过索引可以加快表与表之间的连接。
- 在使用分组和排序子句进行数据检索时，使用索引可以显著地减少查询中分组和排序的时间。

但是索引需要在表基础上创建，需要占用额外的物理空间，而且对表数据进行修改时，比如增删改的时候，需要动态地进行维护，这会降低数据维护的速度。

索引原理

在Oracle数据表中，每一张表都有一个ROWID伪列，这个ROWID是用来唯一标识一条记录所在物理位置的一个id号，每一行对应的ROWID值是固定且唯一的。一旦数据存入数据库就确定，不会在对数据库表操作的过程中发生改变，只有在表发生移动或表空间变化等操作产生物理位置变化时，才发生改变。
如果我们要为emp表的ename这个列建立一个索引，如：

CREATE INDEX idx_emp_ename ON emp(ename);
1

Oracle在创建idx_emp_ename索引时，会对emp表进行一次全表扫描，获取每条记录ename列的数据，并进行升序排序。同时会获取每条记录的ROWID值，连同排序后的ename列一起存储到索引段中，其格式是（索引列值，ROWID），这种组合也称为索引条目。
当检索数据时，比如使用WHERE子句按指定条件检索数据时，Oracle将首先对索引中的列进行快速搜索，由于索引列已经排过序，因此可以使用各种快速的搜索算法，这样就可以避免对全表进行扫描。在找到所要检索的数据后，通过ROWID在emp表中读取具体的记录值。

创建索引

索引的创建方式有两种：
- 自动创建：在定义主键约束或唯一约束时，Oracle自动在相应的约束列sang建立唯一索引。Oracle不推荐人为地创建唯一索引。
- 手动创建：用户可以在其他列上创建非唯一索引。

索引根据其组织形式又可以分为多种类型：
- 单列索引：索引基于单个列创建。
- 复合索引：索引基于多个列创建。
- B树索引：这是Oracle默认使用的索引，B树索引可以是单列索引或复合索引、唯一索引或非唯一索引，索引按B树结构组织并存放索引数据。
- 位图索引：为索引列的每个取值创建一个位图，对表中的每行使用1位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值。
- 函数索引：索引的取值不直接来自列，而是来自包含有列的函数或表达式。

索引的创建语法如下：

CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name ([column1 [ASC|DESC],column2[ASC|DESC],...] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT];
1
2
3
4
5
6
7
8

这些参数的含义如下：
- UNIQUE：表示唯一索引，默认不使用该选项。
- BITMAP：表示位图索引，默认不使用该选项。
- PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间。
- NOLOGGING：表示在创建索引的过程中不产生任何重做日志信息。默认不使用该选项。
- ONLINE：表示在创建或重建索引时，允许对表进行DML操作。默认不使用该选项。
- NOSORT：默认不使用该选项。Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。

要使用CREATE INDEX创建索引，需要具有如下两种权限：
- CREATE INDEX：当在用户所在的方案中创建索引时需要具备的权限。
- CREATE ANY INDEX：当在其他用户方案中创建索引时需要具备的权限。

在创建索引时，会对表进行全表扫描，对索引列的数据进行排序，为索引分配存储空间，将索引的定义信息保存到数据字典中。

如果在使用CREATE INDEX时，不指定任何索引类型参数，默认创建的就是标准的B树索引。

CREATE INDEX idx_emp_empnoname ON emp_index(ename,empno);  --B树索引
CREATE INDEX idx_emp_job ON emp_index(job);                --B树索引
CREATE BITMAP INDEX idx_emp_job_bitmap ON emp_index(job);  --位图索引
CREATE INDEX idx_emp_name ON emp(UPPER(ename));            --函数索引
1
2
3
4

当创建复合索引时，索引列的顺序决定了索引的性能，通常要将最常查询得列放在前面，不常查询的列放在后面。两个具有不同名称的复合索引列，使用了相同的字段但是顺序不同是合法的。

由于索引的创建会带来一定的性能开销，因为必须要注意创建索引的一些基本原则：
- 小表不需要建立索引，比如只有几十条记录的表，不需要建立索引。
- 对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
- 对于大部分列值不重复的列可建立索引。
- 对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。
- 对于列中有许多空值，但经常查询所有的非空值记录的 列，应该建立索引。
- LONG和LONG RAW列不能创建索引。
- 经常进行连接查询的列应该创建索引。
- 在创建复合索引时，将最常查询的列放在其他列的前面。
- 维护索引需要开销，特别是对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，索引多就有好处，但是，如果一个表经常被改，则索引应少点 。
- 在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引。

修改索引

重命名索引

如：

ALTER INDEX idx_emp_empnoname RENAME TO idx_ename_empno;
1

合并和重建索引

表在使用一段时间后，由于频繁的对表进行操作，而每次对表的更新必然伴随着索引的改变，因此，在索引中会产生大量的碎片，从而降低索引的使用效率。可以使用如下两种方式来清理碎片：
- 合并索引：合并索引不改变索引的物理组织结构，只是简单地将B树叶子节点中的存储碎片合并在一起。
- 重建索引：重新创建一个新的索引，删除原来的索引。

合并索引使用ALTER INDEX COALESCE语法，如：

ALTER INDEX idx_ename_empno COALESCE;
1

合并只是简单地将B树中的叶子节点中的碎片合在一起，其实并没有改变索引的物理组织结构。

重建索引实际上就是对原有的索引的删除，再重新建一个新的索引，因为这个原因，所以在使用ALTER INDEX时，可以使用各种存储参数，比如使用STORAGE指定存储参数，使用TABLESPACE指定表空间或利用NOLOGGING选项避免产生重做日志信息。
要重建索引，可以使用语句如：

ALTER INDEX idx_ename_empno REBUILD;
1

也可以使用存储语句更改索引所在的表空间，如：

ALTER INDEX idx_ename_empno REBUILD TABLESPACE users;
1

上面的语句在重建索引的时候，使用TABLESPACE选项将索引移到了users表空间中。

合并索引和重建索引都能够消除索引碎片，但二者在使用上有明显的区别：
- 合并索引不能将索引移动到其他表空间，但重建索引可以。
- 合并索引代价较低，无须额外存储空间，但重建索引恰恰相反。
- 合并索引只能在B树的同一子树中合并，不改变树的高度，但重建索引重建整个B树，可能会降低树的高度。

分配和释放索引空间

在插入或加载数据时，如果表中具有索引，会同时在索引中添加数据，如果索引段空间不足，为了能够向索引段添加数据将导致动态地扩展索引段，从而降低了数据的装载速度。为了避免这个问题，可以在执行装载或大批量插入之前为索引段分配足够的空间，如：

ALTER INDEX idx_ename_empno ALLOCATE EXTENT(SIZE 200K);
1

上述语法首先将idx_ename_empno索引段的索引扩容200KB，以便能容纳所插入的索引数据。
当索引段占用了过多的空间，而实际上用不了这样多的空间时，可以通过DEALLOCATE UNUSED来释放多余的空间，如：

ALTER INDEX idx_ename_empno DEALLOCATE UNUSED;
1

上述语句执行后，将释放未曾使用的索引空间。

删除索引

如：

DROP INDEX idx_ename_empno;
1

在当前用户中删除索引时，需要具备DROP INDEX系统权限。如果是其他用户反感中删除索引，需要有DROP ANY INDEX系统权限。

在删除表时，所有基于该表的索引也会被自动删除。

当以下情况发生时，需要删除索引：
- 索引不再需要时，应该删除以释放所占用的空间。
- 索引没有经常使用，只是极少数查询会使用到该索引时。
- 如果索引中包含损坏的数据块，或者是索引碎片过多，应删除索引，然后重建。
- 如果表数据被移动后导致索引无效，此时因删除该索引，然后重建。
- 当使用SQL*Loader给表中装载大量数据时，系统也会给表的索引增加数据，为了加快装载速度，可以在装载之前删除索引，装载之后重新创建索引。