第11章 索引--《Oracle数据库基础与应用教程》书籍教案

1、人民邮电出版社出版人民邮电出版社出版 於岳编写於岳编写作者联系方式：作者联系方式：本书全面讲述了Oracle数据库的日常管理工作内容。全共14章，包含Oracle简介和安装、简介和安装、Oracle客户端、管理客户端、管理Oracle环境、环境、Oracle体系结构、管理体系结构、管理Oracle存储结构、存储结构、SQL语言、表、约束、视图、同义词和序列、索引、语言、表、约束、视图、同义词和序列、索引、实现数据库安全、实现数据库安全、Data Pump数据导出和导入、数据数据导出和导入、数据库备份和恢复。库备份和恢复。每章结尾提供适量的选择题、简答题和操作题，通过练习和操作实践，帮助读者巩固

2、所学内容。本书适合Oracle爱好者、Oracle工程师、数据库管理员、培训机构以及大专院校计算机专业教师和学生使用。 索引简介索引简介11.1创建索引创建索引11.2修改索引修改索引11.3使用使用OEMOEM管理索引管理索引11.5小结小结11.6删除索引删除索引11.411.1.1 什么是索引什么是索引 在关系数据库中，表中每一行数据都在关系数据库中，表中每一行数据都有一个行唯一标识有一个行唯一标识rowid。 索引是一个与表有关的可选的对象，索引是一个与表有关的可选的对象，使得可以快速地从拥有大量数据的表中查使得可以快速地从拥有大量数据的表中查询数据。询数据。 索引中包含一个索引条目，

3、每一个索索引中包含一个索引条目，每一个索引条目都有一个键值和一个引条目都有一个键值和一个rowid，其中键，其中键值可以是一列或多列的组合。值可以是一列或多列的组合。 索引的作用相当于图书的目录，可索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内以根据目录中的页码快速找到所需的内容。容。 通过在一个表中的一列或多列上创通过在一个表中的一列或多列上创建索引，获得在某些情况下，以检索一建索引，获得在某些情况下，以检索一小部分表中随机分布的行的能力。小部分表中随机分布的行的能力。 存储索引的表空间最好另外单独创存储索引的表空间最好另外单独创建，不要与存储表的表空间在一起。建，不要与存

4、储表的表空间在一起。 索引用于加速数据读取，合理地使索引用于加速数据读取，合理地使用索引可以大大降低用索引可以大大降低I/O次数，从而能够次数，从而能够提高数据访问性能，大大提高数据库的提高数据访问性能，大大提高数据库的运行效率。运行效率。 如果在表上没有索引，那么数据库如果在表上没有索引，那么数据库必须执行全表扫描以查找数据，这种情必须执行全表扫描以查找数据，这种情况会随着数据量的增加而延长数据查询况会随着数据量的增加而延长数据查询的时间。的时间。 索引创建好以后，数据库将自动地维护索引创建好以后，数据库将自动地维护和使用索引。和使用索引。 大量索引存储在表上可能会降低大量索引存储在表上可能

5、会降低DML操操作性能，因为数据库还必须更新索引。作性能，因为数据库还必须更新索引。 不恰当的索引不但不能加快查询速度，不恰当的索引不但不能加快查询速度，反而会降低系统性能。反而会降低系统性能。 因为大量的索引在进行插入、修改和删因为大量的索引在进行插入、修改和删除操作时将会比没有索引花费更多的时间。除操作时将会比没有索引花费更多的时间。 在在Oracle数据库中，索引具有以下数据库中，索引具有以下优点。优点。可以加快表中数据的查询速度。可以加快表中数据的查询速度。通过创建唯一索引，可以保证表中每一通过创建唯一索引，可以保证表中每一行数据的唯一性。行数据的唯一性。可以加速表和表之间的连接，特别

6、是在可以加速表和表之间的连接，特别是在实现数据的参考完整性方面。实现数据的参考完整性方面。使用分组和排序子句进行数据查询时，可使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。以显著减少查询中分组和排序的时间。通过使用索引，可以在查询的过程中，提通过使用索引，可以在查询的过程中，提高系统的性能。高系统的性能。 在在Oracle数据库中，索引具有以下数据库中，索引具有以下缺点。缺点。创建和维护索引需要耗费时间，数据量创建和维护索引需要耗费时间，数据量越大维护时间越长。越大维护时间越长。索引需要占用物理存储空间。索引需要占用物理存储空间。当对表中的数据进行增加、删除和修改当对表

7、中的数据进行增加、删除和修改的时候，索引也需要进行动态维护，这的时候，索引也需要进行动态维护，这样就降低了数据的维护速度。样就降低了数据的维护速度。 在创建索引时，索引列具有以下特在创建索引时，索引列具有以下特点。点。在经常需要查询的列上创建索引，可以在经常需要查询的列上创建索引，可以加快查询的速度。加快查询的速度。在作为主键的列上，强制该列的唯一性在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。和组织表中数据的排列结构。在经常用在连接的列上创建索引，这些在经常用在连接的列上创建索引，这些列主要是一些外键约束，可以加快连接列主要是一些外键约束，可以加快连接的速度。的速度。在经常需要

8、排序的列上创建索引，可以在经常需要排序的列上创建索引，可以加快排序查询时间。加快排序查询时间。在经常使用在经常使用WHERE子句中的列上面创子句中的列上面创建索引，可以加快条件的判断速度。建索引，可以加快条件的判断速度。创建主键和唯一键约束时，将在所在列创建主键和唯一键约束时，将在所在列上自动创建索引。上自动创建索引。对于取值范围很小的列（如性别字段）对于取值范围很小的列（如性别字段）应当创建位图索引。应当创建位图索引。 在表中使用索引时，应该遵循以下在表中使用索引时，应该遵循以下基本原则。基本原则。1在表中插入数据以后再创建索引在表中插入数据以后再创建索引2指定索引数据块空间的使用指定索引数

9、据块空间的使用3索引正确的表和列索引正确的表和列4限制表中索引的数量限制表中索引的数量5合理安排索引列合理安排索引列6根据索引大小设置存储参数根据索引大小设置存储参数 在在Oracle数据库中，可以按照不同数据库中，可以按照不同的标准对索引进行分类。的标准对索引进行分类。 按照索引不同的功能，可以将索引按照索引不同的功能，可以将索引分为分为B树索引、反向键索引、位图索引和树索引、反向键索引、位图索引和基于函数的索引。基于函数的索引。（1）B树索引树索引（2）反向键索引）反向键索引 （3）位图索引）位图索引（4）基于函数的索引）基于函数的索引 按照列数据是否允许重复，可以将按照列数据是否允许重复

10、，可以将索引分为以下类型。索引分为以下类型。 （1）唯一索引）唯一索引 （2）非唯一索引）非唯一索引 按照索引的列的数量，可以将索引按照索引的列的数量，可以将索引分为以下类型。分为以下类型。（1）单列索引）单列索引（2）组合索引）组合索引 CREATE INDEX语句用于在表的一语句用于在表的一个或多个列上创建索引。个或多个列上创建索引。 要在自己的方案中创建索引，必须要在自己的方案中创建索引，必须符合以下条件之一。符合以下条件之一。表必须是在自己的方案中。表必须是在自己的方案中。必须要在表上拥有必须要在表上拥有INDEX对象权限。对象权限。必须要拥有必须要拥有CREATE ANY INDEX

11、系统系统权限。权限。 要在另一个方案中创建索引，必须要要在另一个方案中创建索引，必须要拥有拥有CREATE ANY INDEX系统权限。系统权限。 此外，包含索引的方案的所有者必须此外，包含索引的方案的所有者必须对表空间拥有对表空间拥有UNLIMITED TABLESPACE系统权限，或包含索引的表空间上具有空系统权限，或包含索引的表空间上具有空间配额。间配额。 要创建一个基于函数的索引，要拥要创建一个基于函数的索引，要拥有创建一个常规索引的先决条件，如果有创建一个常规索引的先决条件，如果索引是基于用户定义的函数，那么这些索引是基于用户定义的函数，那么这些函数必须注明函数必须注明DETERMI

12、NISTIC。 此外，如果这些函数的所有者是其此外，如果这些函数的所有者是其他用户，必须在任何用户定义的函数上他用户，必须在任何用户定义的函数上要拥有要拥有EXECUTE对象权限。对象权限。 表表11-1列出了列出了CREATE INDEX语句语句各参数的描述信息。各参数的描述信息。表表11-111-1CREATE INDEXCREATE INDEX语句参数语句参数续表续表 ALTER INDEX语句用于修改或重建语句用于修改或重建已存在的索引。已存在的索引。 要修改索引，该索引必须是在自己要修改索引，该索引必须是在自己的方案中，或者必须拥有的方案中，或者必须拥有ALTER ANY INDEX

13、系统权限。系统权限。 要在其他用户的方案中重建联机索要在其他用户的方案中重建联机索引，必须要拥有引，必须要拥有CREATE ANY INDEX和和CREATE ANY TABLE系统权限。系统权限。 索引是由索引是由Oracle数据库自动维护的，数据库自动维护的，对表进行频繁的操作，索引也会跟着进行对表进行频繁的操作，索引也会跟着进行修改。修改。 当在表中删除一条记录时，当在表中删除一条记录时，Oracle把把相应的索引做一个删除标记，但它依然占相应的索引做一个删除标记，但它依然占据着空间。据着空间。 除非一个块中所有的标记全被删除除非一个块中所有的标记全被删除时，整个块的空间才会被释放。时，

14、整个块的空间才会被释放。 随着时间的推移，索引的查询效率随着时间的推移，索引的查询效率会越来越低，索引的性能就会下降。会越来越低，索引的性能就会下降。 这个时候可以重建一个干净的索引这个时候可以重建一个干净的索引来提高效率。来提高效率。 使用使用REBUILD子句重建现有索引。子句重建现有索引。 如果索引标记为如果索引标记为UNUSABLE，那么，那么一个成功的重建将标记索引为一个成功的重建将标记索引为USABLE。当重建索引时，可以使用现有的索引作为当重建索引时，可以使用现有的索引作为数据源。数据源。 以这种方式创建一个索引，可以更以这种方式创建一个索引，可以更改存储特性或移动到一个新的表空

15、间。改存储特性或移动到一个新的表空间。 基于现有数据源重建索引将删除块基于现有数据源重建索引将删除块内的碎片，减少现有索引中的碎片，相内的碎片，减少现有索引中的碎片，相比于删除索引并使用比于删除索引并使用CREATE INDEX语语句重新创建索引提供了更好的性能。句重新创建索引提供了更好的性能。 表表11-2列出了列出了ALTER INDEX语句各语句各参数的描述信息。参数的描述信息。表表11-211-2ALTER INDEXALTER INDEX语句参数语句参数 使用使用SHRINK SPACE子句压缩索引子句压缩索引段，从而实现收缩索引。段，从而实现收缩索引。 不能为基于函数的索引指定不能

16、为基于函数的索引指定SHRINK SPACE子句。如果索引位于子句。如果索引位于SYSTEM表表空间，则无法进行收缩。空间，则无法进行收缩。 在索引中有可能会有剩余的空间，可以在索引中有可能会有剩余的空间，可以把这些剩余空间整合到一起，起到整合索引把这些剩余空间整合到一起，起到整合索引碎片的作用。碎片的作用。 使用使用COALESCE合并索引块的内容，合并索引块的内容，重新使用空闲块。重新使用空闲块。 合并索引块将受到以下限制。合并索引块将受到以下限制。不能为临时表上的索引合并索引块。不能为临时表上的索引合并索引块。不能为索引组织表的主键索引合并索引不能为索引组织表的主键索引合并索引块。块。

17、使用使用INVISIBLE使得索引不可见，使得索引不可见，被被Oracle优化器忽略，使其无法使用或优化器忽略，使其无法使用或删除它。删除它。 使用使用ALLOCATE EXTENT子句显式子句显式地为索引分配新的区。地为索引分配新的区。 不能为临时表上的索引分配新的区。不能为临时表上的索引分配新的区。 在索引使用过程中，可能会出现空在索引使用过程中，可能会出现空间不足或空间浪费的情况，这时就需要间不足或空间浪费的情况，这时就需要释放未使用的空间。释放未使用的空间。 使用使用DEALLOCATE UNUSED子句子句明确地释放索引末尾未使用的空间，以明确地释放索引末尾未使用的空间，以供在表空间

18、中的其他段使用。供在表空间中的其他段使用。 使用使用PARALLEL子句在索引上为查子句在索引上为查询和询和DML更改默认并行度。更改默认并行度。 设置索引的并行特性，这样索引扫设置索引的并行特性，这样索引扫描将被并行化。描将被并行化。 ENABLE启用基于函数的索引的使启用基于函数的索引的使用。用。 DISABLE禁止基于函数的索引的使禁止基于函数的索引的使用。用。 只能对基于函数的索引进行启用或只能对基于函数的索引进行启用或禁用。禁用。 如果禁用基于函数的索引，那么就如果禁用基于函数的索引，那么就不能将数据插入到索引所在的表中。不能将数据插入到索引所在的表中。 使用使用LOGGING和和N

19、OLOGGING可可以改变索引的日志记录属性，是否生成以改变索引的日志记录属性，是否生成重做日志记录。重做日志记录。 Oracle数据库监视索引以确定它们是数据库监视索引以确定它们是否正在被使用。否正在被使用。 如果索引不被使用，那么就可以删除如果索引不被使用，那么就可以删除该索引，从而消除不必要的语句开销。该索引，从而消除不必要的语句开销。 在在MONITORING时，如果索引被使时，如果索引被使用，用，V$OBJECT_USAGE动态性能视图就动态性能视图就会显示索引被使用。会显示索引被使用。 指定指定UNUSABLE标记索引为无法使用。标记索引为无法使用。当索引标记为当索引标记为UNUS

20、ABLE时，为索引分配的时，为索引分配的空间被立即释放。空间被立即释放。 当索引的状态由当索引的状态由VALID变成变成UNUSABLE时，这个索引会被优化器忽略，并且时，这个索引会被优化器忽略，并且DML操操作也不会维护这个索引。作也不会维护这个索引。 无法使用的索引必须使用无法使用的索引必须使用REBUILD重建，重建，或删除再创建，然后才能使用。或删除再创建，然后才能使用。 如果把基于函数的索引标记为如果把基于函数的索引标记为UNUSABLE ，那么此索引不可用，但是仍然可以插入数据。那么此索引不可用，但是仍然可以插入数据。 使用使用RENAME TO子句重命名索引。子句重命名索引。 n

21、ew_name是索引的新名称，该名称中是索引的新名称，该名称中不包括方案名称。不包括方案名称。 DROP INDEX语句用于删除索引。语句用于删除索引。要删除索引，该索引必须在自己的方案要删除索引，该索引必须在自己的方案中，或者必须要拥有中，或者必须要拥有DROP ANY INDEX系统权限。系统权限。11.5.1 使用使用OEM创建索引创建索引 使用使用Oracle Enterprise Manager按以下按以下步骤创建索引。步骤创建索引。 （1）在）在Oracle Enterprise Manager页面中单页面中单击击【方案方案】【数据库对象数据库对象】【索引索引】，如图如图11-1所

22、示，单击所示，单击【创建创建】按钮。按钮。图图11-1 11-1 索引索引 （2）在图）在图11-2所示【一般信息】页面中，所示【一般信息】页面中，按以下要求输入内容。按以下要求输入内容。图图11-2 11-2 【一般信息一般信息】页面页面 （3）在图）在图11-3所示【存储】页面中，指定所示【存储】页面中，指定表空间、区数、空间使用情况和事物处理表空间、区数、空间使用情况和事物处理数。数。图图11-3 11-3 【存储存储】页面页面 （4）在图）在图11-4所示【选项】页面中，指定所示【选项】页面中，指定索引选项和执行选项，最后单击【确定】索引选项和执行选项，最后单击【确定】按钮。按钮。图图

23、11-4 11-4 【选项选项】页面页面 使用使用Oracle Enterprise Manager按以按以下步骤收缩段。下步骤收缩段。 （1）在图）在图11-5所示页面中，搜索方案所示页面中，搜索方案SYS中的索引中的索引INDEX_1。选择索引。选择索引INDEX_1，在【操作】下拉框中选择【收缩】，然后在【操作】下拉框中选择【收缩】，然后单击【开始】按钮。单击【开始】按钮。图图11-5 11-5 搜索索引搜索索引（2）在图）在图11-6所示页面中，指定搜索选项，所示页面中，指定搜索选项，在此选择【压缩并释放空间】单选框，然在此选择【压缩并释放空间】单选框，然后单击【继续】按钮。后单击【继

24、续】按钮。图图11-6 11-6 收缩段收缩段 （3）在图）在图11-7所示页面中，指定作业名称所示页面中，指定作业名称和调度选项，然后单击【提交】按钮。和调度选项，然后单击【提交】按钮。图图11-7 调度调度 （4）在图）在图11-8所示页面中，显示已经成功所示页面中，显示已经成功地创建了调度程序作业。地创建了调度程序作业。图图11-8 11-8 调度程序作业调度程序作业 使用使用Oracle Enterprise Manager按以下按以下步骤删除索引。步骤删除索引。 （1）在图）在图11-9所示页面中，搜索方案所示页面中，搜索方案SYS中中的索引的索引INDEX_1。选择索引。选择索引INDEX_1，然，然后单击【删除】按钮。后单击【删除】按钮。图图11-9 11-9 搜索索引搜索索引 （2）在图）在图11-10所示页面中，单击【是】所示页面中，单击【是】按钮确认删除索引。按钮确认删除索引。图图11-10 11-10 确认删除索引确认删除索引 索引是一个与表有关的可选的对象，使索引是一个与表有关的可选的对象，使得可以快速地从拥