《Oracle 11g数据库管理与开发基础教程 教学课件 ppt 作者 袁鹏飞 第10章》由会员分享，可在线阅读，更多相关《Oracle 11g数据库管理与开发基础教程 教学课件 ppt 作者 袁鹏飞 第10章（25页珍藏版）》请在金锄头文库上搜索。
1、,Oracle 11g数据库管理与开发基础教程,授课教师： 职务：,第10章 索引,课程描述 索引是关系数据库中的一种基本对象，它是表中数据与相应存储位置的列表。利用索引可以加快数据的检索速度，并实现对完整性的检查。本章介绍Oracle Database 11g中索引的基本概念、创建、维护等内容。,本章知识点,索引的类型 索引的创建 索引的修改和删除 索引的监视,10.1 概述,索引是关系数据库中的一种基本对象，它是表中数据与相应存储位置的列表。 利用索引可以加快数据的检索速度，并实现对完整性的检查。如果索引数量太多，则会影响更新的速度，因为此时要花费更多的时间来更新索引。 索引的创建要遵循以
2、下原则： 依据表的大小创建索引。 依据表和列的特征创建索引。 限制表中索引的数量。 要合理安排复合索引中列的顺序，将频繁使用的列放在其他列的前面。,10.2 创建索引,使用CREATE INDEX语句创建索引，其基本语法格式为： CREATE UNIQUE | BITMAP INDEX schema.index_name ON schema.table_name (column1 ASC | DESC ,column2ASC | DESC . ) TABLESPACE table_space | DEFAULT SORT | NOSORT REVERSE VISIBLE | INVISIBLE
3、 PARALLEL n | NOPARALLEL;,参数说明： UNIQUE、BITMAP：分别指出创建的索引为惟一索引和位图索引，如果未指定这两个选项，创建的则是B-树索引； schema：指出所建索引属于哪个模式，缺省为当前用户； index_name：创建的索引名； column1：指出基表中索引列的列名，一个索引最多可基于16列，long列、long raw列不能建索引； ASC、DESC：指出索引值的排列顺序是升序还是降序，缺省为ASC即升序； ONLINE：指出在创建索引期间允许对表进行DML操作；,TABLESPACE：指定索储索引的表空间； SORT、NOSORT：默认时，Or
4、acle Database在创建索引时将按升序排序索引。如果确认数据库内的数据行已经按升序排序，则可以指定NOSORT，要求在创建索引时不再排序数据行。但此时，如果索引列未按升序排序，则将导致索引创建失败； REVERSE：表示建立反向键值索引； VISIBLE、INVISIBLE：指定索引对优化器是否可见。,10.2.1 B-树索引,B-树索引是Oracle数据库中最常用的一种索引结构，它按照平衡树算法来构造索引，这种索引中的叶子结点保存索引键值和一个指向索引行的ROWID信息。默认情况下，Oracle数据库中创建的索引就是B-树索引。 例如，在books表的bookname列上创建一个非惟
5、一B-索引： BOOKS_PUBorcl_dbs CREATE INDEX book_name_idx ON 2 books(bookname) 3 TABLESPACE USERS; 索引已创建。,Oracle数据库会自动在表的主键约束列和惟一性约束列上创建惟一索引，用户也可以自己进行创建惟一索引。例如，下面代码在books表的booknum列上创建一个惟一索引： BOOKS_PUBorcl_dbs CREATE UNIQUE INDEX booknum_idx ON 2 books(booknum); 索引已创建。,10.2.2 位图索引,位图索引不存储ROWID值，也不存储键值，它用一个
6、索引键条目存储指向多行的指针，即每个索引条目指向多行。位图索引占用空间小，适合索引值基数少，高度重复而且只读的应用环境使用，所以适合于数据仓库等环境。 例如，在orders表的payterms列上创建一个位图索引： BOOKS_PUBorcl_dbs CREATE BITMAP INDEX order_pay_idx 2 ON orders(payterms); 索引已创建。,10.2.3 基于函数的索引,基于函数的索引是在B-树索引或位图索引的基础上，将一个函数计算得到的结果作为索引值而创建的索引。因此，可以把基于函数的索引看做一个虚拟列上的索引。 例如，在authors表的author_l
7、name列创建一个基于函数的索引，以便在该列上执行大小写无关的查询： BOOKS_PUBorcl_dbs CREATE INDEX author_lname_idx 2 ON authors(UPPER(author_lname); 索引已创建。,10.2.4 反向键值索引,反向键值索引通过反向键值保持在索引所有叶子节点上的插入分布。利用反向键值索引可以避免不平衡的索引，使索引条目在索引中分布的更均匀。 通过CREATE INDEX命令直接创建反向键值索引，或在ALTER INDEX命令后加REBUILD NOREVERSE或REBUILD REVERSE子句把索引修改为普通索引或反向键值索引
8、。 例如，在authors表的phone列上创建一个反向键值索引： BOOKS_PUBorcl_dbs CREATE INDEX author_phn_idx 2 ON authors(phone) REVERSE; 索引已创建。,10.2.5 域索引,域索引就是用户自己构建和存储的索引，优化器根据索引的选择和执行的开销决定是否使用该索引。Oracle文本索引就是一种域索引，用于对大量的文本项提供关键字搜索。要完成基于文本的搜索，需要安装Oracle Database 11g中的Oracle Text组件。,数据库在使用过程中，表的数据不断被更新，就会在索引中产生越来越多的存储碎片，DBA可以
9、通过索引的合并操作来清理这些存储碎片。SQL语句的语法为： ALTER INDEX index_name COALESCE DEALLOCATE UNUSED; 参数说明： COALESCE表示合并索引。 DEALLOCATE UNUSED表示合并索引的同时，释放合并后多余的存储空间。 合并的操作只是将B-树索引的叶子节点中的存储碎片合并在一起，并没有改变索引的物理组织结构。,10.3.1 合并索引,10.3.2 重构索引,重构索引也可以清除存储碎片，并且重构操作可以改变索引的存储位置，其语法为： ALTER INDEX index_name REBUILD TABLESPACE table_
10、space; 其中，Tablespace子句为索引指定新的存储表空间。 例如，下面语句对books表中bookname列上的book_name_idx索引进行重构，并把重构后的索引存储到bookspub表空间上： BOOKS_PUBorcl_dbs ALTER INDEX book_name_idx 2 REBUILD 3 TABLESPACE BOOKSPUB; 索引已更改。,10.3.3 重命名索引,使用ALTER INDEX RENAME TO 语句可以对索引进行重命名。 例如，下面代码将索引book_name_idx重命名为bk_idx： BOOKS_PUBorcl_dbs ALTER
11、 INDEX book_name_idx 2 RENAME TO bk_idx; 索引已更改。,10.4 删除索引,对于不再使用或者使用率不高的索引，应该及时删除。使用DROP INDEX语句删除指定的索引。 例如，下面语句删除索引bk_idx： BOOKS_PUBorcl_dbs DROP INDEX bk_idx; 索引已删除。 如果索引是在定义约束时由系统自动建立的，则在禁用或删除约束时，该索引会被自动删除；此外，当表结构被删除时，与其相关的所有索引也随之被删除。,10.5.1 监视索引,要查看某个索引的使用情况，需要对该索引打开监视，然后通过查看动态性能视图V$OBJECT_USAGE
12、获取索引的使用情况。 下面例子说明索引监视的过程： 打开索引监视，以了解索引booknum_idx的使用情况： BOOKS_PUBorcl_dbs ALTER INDEX booknum_idx 2 MONITORING USAGE; 索引已更改。,查询V$OBJECT_USAGE获得索引使用情况信息： BOOKS_PUBorcl_dbs SELECT index_name, used, 2 start_monitoring 3 FROM V$OBJECT_USAGE; INDEX_NAME USED START_MONITORING - - - BOOKNUM_IDX NO 11/10/20
13、12 16:57:53 以上查询结果说明BOOKNUM_IDX索引从上面监视开始还未被用到过。,接下来，先执行下面查询，然后再次查看索引的使用情况： BOOKS_PUBorcl_dbs SELECT * FROM books 2 WHERE booknum = DB1002; BOOKS_PUBorcl_dbs SELECT index_name, used, 2 start_monitoring 3 FROM V$OBJECT_USAGE; INDEX_NAME USED START_MONITORING - - - BOOKNUM_IDX YES 11/10/2012 16:57:53 从
14、查询结果看出，boonum_idx索引已经被使用过,当不需要对索引进行监视时，可以调用ALTER INDEX NOMONITORING USAGE语句关闭监视。例如： BOOKS_PUBorcl_dbs ALTER INDEX booknum_idx 2 NOMONITORING USAGE; 索引已更改。,10.5.2 查询索引信息,索引定义信息存储在Oracle数据库的数据字典内，与索引相关的数据字典和动态性能视图如表10-1所示。,表10-1 与索引定义相关的数据字典和性能视图,例如，下面语句查询当前用户在books表上拥有的索引信息： BOOKS_PUBorcl_dbs SELECT INDEX_NAME, INDEX_TYPE 2 FROM USER_INDEXES 3 WHERE TABLE_NAME = BOOKS; INDEX_NAME INDEX_TYPE - - BOOKS_CONT_IDX DOMAIN BOOK_NAME_IDX NORMAL BOOKNUM_IDX NORMAL SYS_C0010823 NORMAL,本章小结,本章主要介绍了索引的分类和管理操作。 索引是Oracle数据库模式对象的一种，其主要作用是提高数据的查询效率。Oracle Database 11g中提供的索引类型包括： B-树索引。 位图索引。 基于函数的索引。 域索引。,
