第6章 Oracle索引(创建、简介、技巧).ppt

《第6章 Oracle索引(创建、简介、技巧).ppt》由会员分享，可在线阅读，更多相关《第6章 Oracle索引(创建、简介、技巧).ppt（99页珍藏版）》请在优知文库上搜索。

1、本章内容索引是建立在表上的可选对象，设计索引的目的是为了提高查询的速度。但同时索引也会增加系统的负担，进行影响系统的性能。目录可以帮助读者快速查找所需要的内容，数据库中的索引就类似于书的目录。有了索引，DML操作就能快速找到表中的数据，而不需要扫描整张表。因此，对于包含大量数据的表来说，设计索引，可以大大提高操作效率。在书中，目录是内容和页码的清单，而在数据库中，索引是数据和存储位置的列表。6.1 6.1 Oracle索引概述索引概述索引是建立在表上的可选对象。索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。索引在逻辑上和物理上都与相关的表的数据无关，当创建

2、或删除一个索引时，不会影响基本的表、数据库应用或其他索引，当插入、更改和删除相关的表记录时，Oracle会自动管理索引，如果删除索引，所有的应用仍然可以继续工作。因此，在表上创建索引不会对表的使用产生任何影响，但是，在表中的一列或多列上创建索引可以为数据的检索提供快捷的存取路径，提高检索速度。索引一旦建立后，当在表上进行DML操作时，Oracle会自动维护索引，并决定何时使用索引。索引的使用对用户是透明的，用户不需要在执行SQL语句时指定使用哪个索引及如何使用索引，也就是说，无论表上是否创建有索引，SQL语句的用法不变。用户在进行操作时，不需要考虑索引的存在，索引只与系统性能相关。当在一个没有

3、创建索引的表中查询符合某个条件的记录时，DBMS会顺序地逐条读取每个记录与查询条件进行匹配，这种方式称为全表扫描。全表扫描方式需要遍历整个表，效率很低。假设SALES表的数据如表所示。ROWID伪列表示记录的物理存储位置。SALES表的TOPIC列没有特定的顺序。现在查询TOPIC为PEN 的记录。由于在TOPIC列上没有索引，该语句会搜索所有的记录。因为即使找到了PEN也不能保证表中只有一个PEN，必须全部搜索一遍。在TOPIC列上建立索引，Oracle对全表进行一次搜索，将每条记录的TOPIC值按升序排列，然后构建索引条目，即（TOPIC值，ROWID值），存储到索引段中。当查询PEN的记

4、录时，如何查找？Oracle支持多种类型的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。1单列索引和复合索引 2B树索引 3位图索引 4函数索引 1.1.单列索引和复合索引单列索引和复合索引 一个索引可以由一个或多个列组成。基于单个列所创建的索引称为单列索引，基于两列或多列所创建的索引称为多列索引。2.B 2.B树索引树索引 B树索引是Oracle数据库中最常用的一种索引。当使用CREATE INDEX语句创建索引时，默认创建的索引就是B树索引。B树索引是按B树结构或使用B树算法组织并存储索引数据的。B树索引就是一棵二叉树，它由根、分支节

5、点和叶子节点三部分构成。其中，根包含指向分支节点的信息，分支节点包含指向下级分支节点和指向叶子节点的信息，叶子节点包含索引列和指向表中每个匹配行的ROWID值。叶子节点是一个双向链表，因此可以对其进行任何方面的范围扫描。2.B 2.B树索引树索引 B树索引中所有叶子节点都具有相同的深度，所以不管查询条件如何，查询速度基本相同。另外，B树索引能够适应各种查询条件，包括精确查询、模糊查询和比较查询。2.B 2.B树索引树索引 B树索引的分类如下所示。Unique：唯一索引，其索引值不能重复，但允许为NULL。在创建索引时指定UNIQUE关键字可以创建唯一索引。当建立“主键约束条件”时Oracle会

6、自动在相应列上建立唯一索引，主键列不允许为NULL。Non-Unique：非唯一索引，其索引值可以重复，允许为NULL。默认情况下，Oracle创建的索引是非唯一索引。Reverse Key：反向关键字索引。通过在创建索引时指定“REVERSE”关键字，可以创建反向关键字索引，被索引的每个数据列中的数据都是反向存储的，但仍然保持原来数据列的次序。3.3.位图索引位图索引 在B树索引中，保存的是经排序过的索引列及其对应的ROWID值。但是对于一些基数很小的列来说，这样做并不能显著提高查询的速度。所谓基数，是指某个列可能拥有的不重复值的个数。比如性别列的基数为2（只有男和女）。因此，对于象性别、婚

7、姻状况、政治面貌等只具有几个固定值的字段而言，如果要建立索引，应该建立位图索引，而不是默认的B树索引。3.3.位图索引位图索引 当创建位图索引时，Oracle会扫描整张表，并为索引列的每个取值建立一个位图。在这个位图中，对表中每一行使用一位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值，如果为1，则表示该位对应的ROWID所在的记录包含该位图索引列值。最后通过位图索引中的映射函数完成位到行的ROWID的转换。3.3.位图索引位图索引 4.4.函数索引函数索引 前面的索引都是直接对表中的列创建索引，除此之外，Oracle还可以对包含有列的函数或表达式创建索引，这就是函数索引。当需

8、要经常访问一些函数或表达式时，可以将其存储在索引中，当下次访问时，由于该值已经计算出来了，因此，可以大大提高那些在WHERE子句中包含该函数或表达式的查询操作的速度。4.4.函数索引函数索引 函数索引既可以使用B树索引，也可以使用位图索引，可以根据函数或表达式的结果的基数大小来进行选择，当函数或表达式的结果不确定时采用B树索引，当函数或表达式的结果是固定的几个值时采用位图索引。4.4.函数索引函数索引 下面通过一个例子看看函数索引的用法。在SALES表中，TOPIC列的值如果采用首字母大写的方式存储。4.4.函数索引函数索引 现在使用下列代码查询：SELECT*FROM SALES WHERE

9、 TOPIC=TEE;将没有结果。现在忽略大小写，将代码修改如下：SELECT*FROM SALES WHERE UPPER(TOPIC)=TEE;这样可以查到相应的结果，但是，由于不是直接查询TOPIC列，所以，即使在TOPIC列上创建了索引也无法使用。4.4.函数索引函数索引 这时，就可以使用函数索引，创建函数索引的代码如下：C R E A T E I N D E X f u n i d x _ u p p e r _ t o p i c O N SALES(UPPER(TOPIC);由于函数索引存储了预先计算过的值，因此，查询时不需要对每条记录都再计算一次WHERE条件，从而可以提高查询

10、的速度。在函数索引中可以使用各种算术运算符、PL/SQL函数和内置SQL函数，如LEN、TRIM、SUBSTR等。这些函数的共同特点是为每行返回独立的结果，因此，象集函数（如SUM、MAX、MIN、AVG等）不能使用。使用索引的目的是为了提高系统的效率，但同时它也会增加系统的负担，进行影响系统的性能，因为系统必须在进行DML操作后维护索引数据。在新的SQL标准中并不推荐使用索引，而是建议在创建表的时候用主键替代。因此，为了防止使用索引后反而降低系统的性能，应该遵循一些基本的原则。使用索引应该遵循以下一些基本的原则。1小表不需要建立索引。2对于大表而言，如果经常查询的记录数目少于表中总记录数目的

11、15%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。3对于大部分列值不重复的列可建立索引。4对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。5对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。6LONG和LONG RAW列不能创建索引。7经常进行连接查询的列上应该创建索引。8在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。9维护索引需要开销，特别时对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，则索引多就有好处，但是，一个表如果经常被更改，则索引应少点。10在表中插入数据后创建索引。如果在装载

12、数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引。字符类LONG:可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，可以存储长文本一个表中最多一个LONG列二进制类 LONGRAW:可变长二进制数据，最长2G LONG使用说明：1、LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。2、对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。3、LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。4、很多工具，包括SQL*Plus，处理LONG 数据类型都

13、是很困难的。5、LONG 数据类型的使用中，要受限于磁盘的大小。能够操作 LONG 的 SQL 语句：1、Select语句 2、Update语句中的SET语句 3、Insert语句中的VALUES语句 限制：1、一个表中只能包含一个 LONG 类型的列。2、不能索引LONG类型列。3、不能将含有LONG类型列的表作聚簇。4、不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into.select。5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。限制：6、不能对LONG类型列加约束条件（NULL

14、、NOT NULL、DEFAULT除外），如：关键字列(PRIMARY KEY)不能是 LONG 数据类型。7、LONG类型列不能用在Select的以下子句中：where、group by、order by，以及带有distinct的select语句中。8、LONG类型列不能用于分布查询。9、PL/SQL过程块的变量不能定义为LONG类型。10、LONG类型列不能被SQL函数所改变，如：substr、instr。SQL*Plus 中操作LONG类型列：1、set long n 2、col 列名 format Ann代表n位字符（n为大于零的整数）An表示将此列的数据显示宽度限制为不超过n位。创

15、建索引使用CREATE INDEX语句。在用户自己的方案中创建索引，需要CREATE INDEX系统权限，在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外，索引需要存储空间，因此，还必须在保存索引的表空间中有配额，或者具有UNLIMITED TABLESPACE系统权限。6.2 6.2 创建创建索引索引CREATE INDEX语句的语法如下：CREATE UNIQUE|BITMAP INDEX index_nameON table_name(column1 ASC|DESC,column2ASC|DESC,|express)TABLESPACE tablespac

16、e_namePCTFREE n1STORAGE(INITIAL n2)NOLOGGINGNOLINENOSORT;6.2 6.2 创建创建索引索引其中：UNIQUE：表示唯一索引，默认情况下，不使用该选项。BITMAP：表示创建位图索引，默认情况下，不使用该选项。PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间。NOLOGGING：表示在创建索引的过程中不产生任何重做日志信息。默认情况下，不使用该选项。ONLINE：表示在创建或重建索引时，允许对表进行DML操作。默认情况下，不使用该选项。NOSORT：默认情况下，不使用该选项。则Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。6.2 6.2 创建创建索引索引可以在一个表上创建多个索引，但这些索引的列的组合必须不同。如下列的索引是合法的。CREATE INDEX idx1 ON SALES(ID,TOPIC)CREATE INDEX idx2 ON SALES(TOPIC,ID)其中，idx1和idx2索引都使用了ID和TOPIC