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

《Oracle数据库基础与应用教程》书籍教案人民邮电出版社出版於岳编写作者联系方式：[e-mail]本书内容：本书全面讲述了Oracle数据库的日常管理工作内容。全共14章，包含Oracle简介和安装、Oracle客户端、管理Oracle环境、Oracle体系结构、管理Oracle存储结构、SQL语言、表、约束、视图、同义词和序列、索引、实现数据库安全、DataPump数据导出和导入、数据库备份和恢复。每章结尾提供适量的选择题、简答题和操作题，通过练习和操作实践，帮助读者巩固所学内容。本书适合Oracle爱好者、Oracle工程师、数据库管理员、培训机构以及大专院校计算机专业教师和学生使用。第11章索引索引简介11.1创建索引11.2修改索引11.3使用OEM管理索引11.5小结11.6删除索引11.411.1索引简介11.1.1什么是索引在关系数据库中，表中每一行数据都有一个行唯一标识rowid。索引是一个与表有关的可选的对象，使得可以快速地从拥有大量数据的表中查询数据。索引中包含一个索引条目，每一个索引条目都有一个键值和一个rowid，其中键值可以是一列或多列的组合。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。通过在一个表中的一列或多列上创建索引，获得在某些情况下，以检索一小部分表中随机分布的行的能力。存储索引的表空间最好另外单独创建，不要与存储表的表空间在一起。索引用于加速数据读取，合理地使用索引可以大大降低I/O次数，从而能够提高数据访问性能，大大提高数据库的运行效率。如果在表上没有索引，那么数据库必须执行全表扫描以查找数据，这种情况会随着数据量的增加而延长数据查询的时间。索引创建好以后，数据库将自动地维护和使用索引。大量索引存储在表上可能会降低DML操作性能，因为数据库还必须更新索引。不恰当的索引不但不能加快查询速度，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时将会比没有索引花费更多的时间。11.1.2索引优缺点在Oracle数据库中，索引具有以下优点。可以加快表中数据的查询速度。通过创建唯一索引，可以保证表中每一行数据的唯一性。可以加速表和表之间的连接，特别是在实现数据的参考完整性方面。使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。通过使用索引，可以在查询的过程中，提高系统的性能。在Oracle数据库中，索引具有以下缺点。创建和维护索引需要耗费时间，数据量越大维护时间越长。索引需要占用物理存储空间。当对表中的数据进行增加、删除和修改的时候，索引也需要进行动态维护，这样就降低了数据的维护速度。11.1.3创建索引的列的特点在创建索引时，索引列具有以下特点。在经常需要查询的列上创建索引，可以加快查询的速度。在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。在经常用在连接的列上创建索引，这些列主要是一些外键约束，可以加快连接的速度。在经常需要排序的列上创建索引，可以加快排序查询时间。在经常使用WHERE子句中的列上面创建索引，可以加快条件的判断速度。创建主键和唯一键约束时，将在所在列上自动创建索引。对于取值范围很小的列（如性别字段）应当创建位图索引。11.1.4索引使用原则在表中使用索引时，应该遵循以下基本原则。1．在表中插入数据以后再创建索引2．指定索引数据块空间的使用3．索引正确的表和列4．限制表中索引的数量5．合理安排索引列6．根据索引大小设置存储参数11.1.5索引分类在Oracle数据库中，可以按照不同的标准对索引进行分类。1．按照索引不同的功能分类按照索引不同的功能，可以将索引分为B树索引、反向键索引、位图索引和基于函数的索引。（1）B树索引（2）反向键索引（3）位图索引（4）基于函数的索引2．按照列数据是否允许重复分类按照列数据是否允许重复，可以将索引分为以下类型。（1）唯一索引（2）非唯一索引3．按照索引的列的数量分类按照索引的列的数量，可以将索引分为以下类型。（1）单列索引（2）组合索引11.2创建索引CREATEINDEX语句用于在表的一个或多个列上创建索引。要在自己的方案中创建索引，必须符合以下条件之一。表必须是在自己的方案中。必须要在表上拥有INDEX对象权限。必须要拥有CREATEANYINDEX系统权限。要在另一个方案中创建索引，必须要拥有CREATEANYINDEX系统权限。此外，包含索引的方案的所有者必须对表空间拥有UNLIMITEDTABLESPACE系统权限，或包含索引的表空间上具有空间配额。要创建一个基于函数的索引，要拥有创建一个常规索引的先决条件，如果索引是基于用户定义的函数，那么这些函数必须注明DETERMINISTIC。此外，如果这些函数的所有者是其他用户，必须在任何用户定义的函数上要拥有EXECUTE对象权限。表11-1列出了CREATEINDEX语句各参数的描述信息。表11-1CREATEINDEX语句参数续表11.3修改索引ALTERINDEX语句用于修改或重建已存在的索引。要修改索引，该索引必须是在自己的方案中，或者必须拥有ALTERANYINDEX系统权限。要在其他用户的方案中重建联机索引，必须要拥有CREATEANYINDEX和CREATEANYTABLE系统权限。11.3.1重建现有索引索引是由Oracle数据库自动维护的，对表进行频繁的操作，索引也会跟着进行修改。当在表中删除一条记录时，Oracle把相应的索引做一个删除标记，但它依然占据着空间。除非一个块中所有的标记全被删除时，整个块的空间才会被释放。随着时间的推移，索引的查询效率会越来越低，索引的性能就会下降。这个时候可以重建一个干净的索引来提高效率。使用REBUILD子句重建现有索引。如果索引标记为UNUSABLE，那么一个成功的重建将标记索引为USABLE。当重建索引时，可以使用现有的索引作为数据源。以这种方式创建一个索引，可以更改存储特性或移动到一个新的表空间。基于现有数据源重建索引将删除块内的碎片，减少现有索引中的碎片，相比于删除索引并使用CREATEINDEX语句重新创建索引提供了更好的性能。表11-2列出了ALTERINDEX语句各参数的描述信息。表11-2ALTERINDEX语句参数11.3.2收缩索引使用SHRINKSPACE子句压缩索引段，从而实现收缩索引。不能为基于函数的索引指定SHRINKSPACE子句。如果索引位于SYSTEM表空间，则无法进行收缩。11.3.3合并索引块在索引中有可能会有剩余的空间，可以把这些剩余空间整合到一起，起到整合索引碎片的作用。使用COALESCE合并索引块的内容，重新使用空闲块。合并索引块将受到以下限制。不能为临时表上的索引合并索引块。不能为索引组织表的主键索引合并索引块。11.3.4使得索引不可见使用INVISIBLE使得索引不可见，被Oracle优化器忽略，使其无法使用或删除它。11.3.5为索引分配新区使用ALLOCATEEXTENT子句显式地为索引分配新的区。不能为临时表上的索引分配新的区。11.3.6释放未使用的空间在索引使用过程中，可能会出现空间不足或空间浪费的情况，这时就需要释放未使用的空间。使用DEALLOCATEUNUSED子句明确地释放索引末尾未使用的空间，以供在表空间中的其他段使用。11.3.7设置索引并行特性使用PARALLEL子句在索引上为查询和DML更改默认并行度。设置索引的并行特性，这样索引扫描将被并行化。11.3.8启用或禁用基于函数的索引ENABLE启用基于函数的索引的使用。DISABLE禁止基于函数的索引的使用。只能对基于函数的索引进行启用或禁用。如果禁用基于函数的索引，那么就不能将数据插入到索引所在的表中。11.3.9指定日志记录属性使用LOGGING和NOLOGGING可以改变索引的日志记录属性，是否生成重做日志记录。11.3.10监视索引的使用Oracle数据库监视索引以确定它们是否正在被使用。如果索引不被使用，那么就可以删除该索引，从而消除不必要的语句开销。在MONITORING时，如果索引被使用，V$OBJECT_USAGE动态性能视图就会显示索引被使用。11.3.11标记索引无法使用指定UNUSABLE标记索引为无法使用。当索引标记为UNUSABLE时，为索引分配的空间被立即释放。当索引的状态由VALID变成UNUSABLE时，这个索引会被优化器忽略，并且DML操作也不会维护这个索引。无法使用的索引必须使用REBUILD重建，或删除再创建，然后才能使用。如果把基于函数的索引标记为UNUSABLE，那么此索引不可用，但是仍然可以插入数据。11.3.12重命名索引使用RENAMETO子句重命名索引。new_name是索引的新名称，该名称中不包括方案名称。11.4删除索引DROPINDEX语句用于删除索引。要删除索引，该索引必须在自己的方案中，或者必须要拥有DROPANYINDEX系统权限。11.5使用OEM管理索引11.5.1使用OEM创建索引使用OracleEnterpriseManager按以下步骤创建索引。（1）在OracleEnterpriseManager页面中单击【方案】→【数据库对象】→【索引】，如图11-1所示，单击【创建】按钮。图11-1索引（2）在图11-2所示【一般信息】页面中，按以下要求输入内容。图11-2【一般信息】页面（3）在图11-3所示【存储】页面中，指定表空间、区数、空间使用情况和事物处理数。图11-3【存储】页面（4）在图11-4所示【选项】页面中，指定索引选项和执行选项，最后单击【确定】按钮。图11-4【选项】页面11.5.2使用OEM收缩段使用OracleEnterpriseManager按以下步骤收缩段。（1）在图11-5所示页面中，搜索方案SYS中的索引INDEX_1。选择索引INDEX_1，在【操作】下拉框中选择【收缩】，然后单击【开始】按钮。图11-5搜索索引（2）在图11-6所示页面中，指定搜索选项，在此选择【压缩并释放空间】单选框，然后单击【继续】按钮。图11-6收缩段（3）在图11-7所示页面中，指定作业名称和调度选项，然后单击【提交】按钮。图11-7调度（4）在图11-8所示页面中，显示已经成功地创建了调度程序作业。图11-8调度程序作业11.5.3使用OEM删除索引使用OracleEnterpriseManager按以下步骤删除索引。（1）在图11-9所示页面中，搜索方案SYS中的索引INDEX_1。选择索引INDEX_1，然后单击【删除】按钮。图11-9搜索索引（2）在图11-10所示页面中，单击【是】按钮确认删除索引。图11-10确认删除索引11.6小结索引是一个与表有关的可选的对象，使得可以快速地从拥有大量数据的表中查询数据。索引中包含一个索引条目，每一个索引条目都有一个键值和一个rowid，其中，键值可以是一列或者多列的组合。索引的作用相当于图书的目录，可以根据目录中的页码快速地找到所需的内容。索引用于加速数据读取，合理的使用索引可以大大降低I/O次数，从而能够提高数据访问性能，大大提高数据库的运行效率。索引创建好以后，数据库将自动维护和使用索引。不恰当的索引不但不能加快查询速度，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的时间。在Oracle数据库中，按照不同的功能，可以将索引分为B树索引、反向键索引、位图索引和基于函数的索引。按照列数据是否允许重复，可以将索引分为唯一索引和非唯一索引。按照索引的列的数量，可以将索引分为单列索引和组合索引。CREATEINDEX语句用于在表的一个或多个列上创建索引。ALTERINDEX语句用于修改或重建已存在的索引。DROPINDEX语句用于删除索引。