第6章 Oracle索引(创建、简介、技巧).ppt
《第6章 Oracle索引(创建、简介、技巧).ppt》由会员分享,可在线阅读,更多相关《第6章 Oracle索引(创建、简介、技巧).ppt(99页珍藏版)》请在三一文库上搜索。
1、Oracle_索引,第6章,Oracle9i关系数据库,本章内容,6.1 索引概述 6.2 创建索引 6.3 修改索引 6.4 查看索引,一、索引的概念 索引是建立在表上的可选对象,设计索引的目的是为了提高查询的速度。但同时索引也会增加系统的负担,进行影响系统的性能。 目录可以帮助读者快速查找所需要的内容,数据库中的索引就类似于书的目录。有了索引,DML操作就能快速找到表中的数据,而不需要扫描整张表。因此,对于包含大量数据的表来说,设计索引,可以大大提高操作效率。在书中,目录是内容和页码的清单,而在数据库中,索引是数据和存储位置的列表。,6.1 Oracle索引概述,一、索引的概念 索引是建立
2、在表上的可选对象。 索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式,从而提高检索效率。 索引在逻辑上和物理上都与相关的表的数据无关,当创建或删除一个索引时,不会影响基本的表、数据库应用或其他索引,当插入、更改和删除相关的表记录时,Oracle会自动管理索引,如果删除索引,所有的应用仍然可以继续工作。因此,在表上创建索引不会对表的使用产生任何影响,但是,在表中的一列或多列上创建索引可以为数据的检索提供快捷的存取路径,提高检索速度。,一、索引的概念 索引一旦建立后,当在表上进行DML操作时,Oracle会自动维护索引,并决定何时使用索引。 索引的使用对用户是透明的,用户不需要在执
3、行SQL语句时指定使用哪个索引及如何使用索引,也就是说,无论表上是否创建有索引,SQL语句的用法不变。用户在进行操作时,不需要考虑索引的存在,索引只与系统性能相关。,二、索引的原理 当在一个没有创建索引的表中查询符合某个条件的记录时,DBMS会顺序地逐条读取每个记录与查询条件进行匹配,这种方式称为全表扫描。 全表扫描方式需要遍历整个表,效率很低。,二、索引的原理 假设SALES表的数据如表所示。 ROWID伪列表示记录的物理存储位置。SALES表的TOPIC列没有特定的顺序。 现在查询TOPIC为PEN 的记录。由于在TOPIC列上没有索引,该语句会搜索所有的记录。因为即使找到了PEN也不能保
4、证表中只有一个PEN,必须全部搜索一遍。,二、索引的原理 在TOPIC列上建立索引,Oracle对全表进行一次搜索,将每条记录的TOPIC值按升序排列,然后构建索引条目,即(TOPIC值,ROWID值),存储到索引段中。 当查询PEN的记录时,如何查找?,三、索引的类型 Oracle支持多种类型的索引,可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类,以满足各种表和查询条件的要求。 1单列索引和复合索引 2B树索引 3位图索引 4函数索引,1. 单列索引和复合索引 一个索引可以由一个或多个列组成。 基于单个列所创建的索引称为单列索引,基于两列或多列所创建的索引称为多列索引。,2
5、. B树索引 B树索引是Oracle数据库中最常用的一种索引。当使用CREATE INDEX语句创建索引时,默认创建的索引就是B树索引。 B树索引是按B树结构或使用B树算法组织并存储索引数据的。B树索引就是一棵二叉树,它由根、分支节点和叶子节点三部分构成。其中,根包含指向分支节点的信息,分支节点包含指向下级分支节点和指向叶子节点的信息,叶子节点包含索引列和指向表中每个匹配行的ROWID值。叶子节点是一个双向链表,因此可以对其进行任何方面的范围扫描。,2. B树索引 B树索引中所有叶子节点都具有相同的深度,所以不管查询条件如何,查询速度基本相同。另外,B树索引能够适应各种查询条件,包括精确查询、
6、模糊查询和比较查询。,2. B树索引 B树索引的分类如下所示。 Unique:唯一索引,其索引值不能重复,但允许为NULL。在创建索引时指定UNIQUE关键字可以创建唯一索引。当建立“主键约束条件”时Oracle会自动在相应列上建立唯一索引,主键列不允许为NULL。 Non-Unique:非唯一索引,其索引值可以重复,允许为NULL。默认情况下,Oracle创建的索引是非唯一索引。 Reverse Key:反向关键字索引。通过在创建索引时指定“REVERSE”关键字,可以创建反向关键字索引,被索引的每个数据列中的数据都是反向存储的,但仍然保持原来数据列的次序。,3. 位图索引 在B树索引中,保
7、存的是经排序过的索引列及其对应的ROWID值。但是对于一些基数很小的列来说,这样做并不能显著提高查询的速度。所谓基数,是指某个列可能拥有的不重复值的个数。比如性别列的基数为2(只有男和女)。 因此,对于象性别、婚姻状况、政治面貌等只具有几个固定值的字段而言,如果要建立索引,应该建立位图索引,而不是默认的B树索引。,3. 位图索引 当创建位图索引时,Oracle会扫描整张表,并为索引列的每个取值建立一个位图。在这个位图中,对表中每一行使用一位(bit,取值为0或1)来表示该行是否包含该位图的索引列的取值,如果为1,则表示该位对应的ROWID所在的记录包含该位图索引列值。最后通过位图索引中的映射函
8、数完成位到行的ROWID的转换。,3. 位图索引,4. 函数索引 前面的索引都是直接对表中的列创建索引,除此之外,Oracle还可以对包含有列的函数或表达式创建索引,这就是函数索引。 当需要经常访问一些函数或表达式时,可以将其存储在索引中,当下次访问时,由于该值已经计算出来了,因此,可以大大提高那些在WHERE子句中包含该函数或表达式的查询操作的速度。,4. 函数索引 函数索引既可以使用B树索引,也可以使用位图索引,可以根据函数或表达式的结果的基数大小来进行选择,当函数或表达式的结果不确定时采用B树索引,当函数或表达式的结果是固定的几个值时采用位图索引。,4. 函数索引 下面通过一个例子看看函
9、数索引的用法。在SALES表中,TOPIC列的值如果采用首字母大写的方式存储。,4. 函数索引 现在使用下列代码查询: SELECT * FROM SALES WHERE TOPIC=TEE; 将没有结果。现在忽略大小写,将代码修改如下: SELECT * FROM SALES WHERE UPPER(TOPIC)=TEE; 这样可以查到相应的结果,但是,由于不是直接查询TOPIC列,所以,即使在TOPIC列上创建了索引也无法使用。,4. 函数索引 这时,就可以使用函数索引,创建函数索引的代码如下: CREATE INDEX funidx_upper_topic ON SALES(UPPER(
10、TOPIC); 由于函数索引存储了预先计算过的值,因此,查询时不需要对每条记录都再计算一次WHERE条件,从而可以提高查询的速度。 在函数索引中可以使用各种算术运算符、PL/SQL函数和内置SQL函数,如LEN、TRIM、SUBSTR等。这些函数的共同特点是为每行返回独立的结果,因此,象集函数(如SUM、MAX、MIN、AVG等)不能使用。,四、管理索引的原则 使用索引的目的是为了提高系统的效率,但同时它也会增加系统的负担,进行影响系统的性能,因为系统必须在进行DML操作后维护索引数据。 在新的SQL标准中并不推荐使用索引,而是建议在创建表的时候用主键替代。因此,为了防止使用索引后反而降低系统
11、的性能,应该遵循一些基本的原则。,四、管理索引的原则 使用索引应该遵循以下一些基本的原则。 1小表不需要建立索引。 2对于大表而言,如果经常查询的记录数目少于表中总记录数目的15%时,可以创建索引。这个比例并不绝对,它与全表扫描速度成反比。 3对于大部分列值不重复的列可建立索引。 4对于基数大的列,适合建立B树索引,而对于基数小的列适合建立位图索引。 5对于列中有许多空值,但经常查询所有的非空值记录的列,应该建立索引。,四、管理索引的原则 6LONG和LONG RAW列不能创建索引。 7经常进行连接查询的列上应该创建索引。 8在使用CREATE INDEX语句创建查询时,将最常查询的列放在其他
12、列前面。 9维护索引需要开销,特别时对表进行插入和删除操作时,因此要限制表中索引的数量。对于主要用于读的表,则索引多就有好处,但是,一个表如果经常被更改,则索引应少点。 10在表中插入数据后创建索引。如果在装载数据之前创建了索引,那么当插入每行时,Oracle都必须更改每个索引。,字符类 LONG:可变长的字符串数据,最长2G,LONG具有VARCHAR2列的特性,可以存储长文本一个表中最多一个LONG列 二进制类 LONG RAW: 可变长二进制数据,最长2G,LONG使用说明: 1、LONG 数据类型中存储的是可变长字符串,最大长度限制是2GB。 2、对于超出一定长度的文本,基本只能用LO
13、NG类型来存储,数据字典中很多对象的定义就是用LONG来存储的。 3、LONG类型主要用于不需要作字符串搜索的长串数据,如果要进行字符搜索就要用varchar2类型。 4、很多工具,包括SQL*Plus,处理LONG 数据类型都是很困难的。 5、LONG 数据类型的使用中,要受限于磁盘的大小。,能够操作 LONG 的 SQL 语句: 1、Select语句 2、Update语句中的SET语句 3、Insert语句中的VALUES语句,限制: 1、一个表中只能包含一个 LONG 类型的列。 2、不能索引LONG类型列。 3、不能将含有LONG类型列的表作聚簇。 4、不能在SQL*Plus中将LON
14、G类型列的数值插入到另一个表格中,如insert into .select。 5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。,限制: 6、不能对LONG类型列加约束条件(NULL、NOT NULL、DEFAULT除外),如:关键字列(PRIMARY KEY)不能是 LONG 数据类型。 7、LONG类型列不能用在Select的以下子句中:where、group by、order by,以及带有distinct的select语句中。 8、LONG类型列不能用于分布查询。 9、PL/SQL过程块的变量不能定义为LONG类型。
15、10、LONG类型列不能被SQL函数所改变,如:substr、instr。,SQL*Plus 中操作LONG类型列: 1、set long n 2、col 列名 format An n代表n位字符(n为大于零的整数) An表示将此列的数据显示宽度限制为不超过n位。,创建索引使用CREATE INDEX语句。 在用户自己的方案中创建索引,需要CREATE INDEX系统权限,在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外,索引需要存储空间,因此,还必须在保存索引的表空间中有配额,或者具有UNLIMITED TABLESPACE系统权限。,6.2 创建索引,CREA
16、TE INDEX语句的语法如下: 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;,6.2 创建索引,其中: UNIQUE:表示唯一索引,默认情况下,不使用该选项。 BITMAP:表示创建位图索引,默认情况下,不使用该选项。 PCTFREE:指定索引在数据块中的空闲空间。对于经常插
17、入数据的表,应该为表中索引指定一个较大的空闲空间。 NOLOGGING:表示在创建索引的过程中不产生任何重做日志信息。默认情况下,不使用该选项。 ONLINE:表示在创建或重建索引时,允许对表进行DML操作。默认情况下,不使用该选项。 NOSORT:默认情况下,不使用该选项。则Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的,则可以使用该选项。,6.2 创建索引,可以在一个表上创建多个索引,但这些索引的列的组合必须不同。如下列的索引是合法的。 CREATE INDEX idx1 ON SALES(ID,TOPIC) CREATE INDEX idx2 ON SA
18、LES(TOPIC,ID) 其中,idx1和idx2索引都使用了ID和TOPIC列,但由于顺序不同,因此是合法的。,6.2 创建索引,一、创建B树索引 B树索引是Oracle默认的索引类型,当在WHERE子句中经常要引用某些列时,应该在这些列上创建索引。 例如,经常需要在SALES表的TOPIC列上按标题查询,就可以在TOPIC列上建立B树索引。,1在OEM中,打开SALES表项,右键单击“索引”项,选择“创建”,如图所示。,2出现创建索引的界面,其“一般信息”页,如图所示。,3单击TOPIC列,在其上创建名为“标题索引”的索引,结果如图所示。,4单击“创建”按钮,创建索引成功,如图所示。,5
19、在OEM中可以查看刚创建的索引,如图所示。,6在“SQL Plus Worksheet”中执行以下SQL代码也可创建该索引。如图所示。 CREATE INDEX “SCOTT“.“标题索引“ ON “SCOTT“.“SALES“(“TOPIC“) ;,7下面查询TOPIC为TEE的记录,看看是否用到了刚创建的“标题索引”。打开“SQL Scratchpad”工具,如图所示。,8在“SQL Scratchpad”中输入如下代码,然后单击“解释计划”按钮。如图所示。 SELECT * FROM “SCOTT“.“SALES“ WHERE “TOPIC“=TEE;,9解释计划的结果如图所示。 从解释
20、计划中可以看到,该查询已经使用了刚才创建的B树索引“标题索引”。,二、创建位图索引 位图索引适合于那些基数较少,且经常对该列进行查询、统计的列。 下面,我们以SALES表的ISLOOK列(只有Y和N这两个值)为例介绍如何创建位图索引。,1在OEM中,打开SALES表项,右键单击“索引”项,选择“创建”,如图所示。,2出现创建索引的界面,在“一般信息”页中单击ISLOOK列,在其上创建名为“是否看过该商品索引”的位图索引,结果如图所示。,3单击“创建”按钮,创建索引成功,如图所示。,4在OEM中可以查看刚创建的索引,如图所示。,5在“SQL Plus Worksheet”中执行以下SQL代码也可
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第6章 Oracle索引创建、简介、技巧 Oracle 索引 创建 简介 技巧
链接地址:https://www.31doc.com/p-5030266.html