Oracle索引详解
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
使用索引应该遵循以下一些基本的原则。 1．小表不需要建立索引。 2．对于大表而言，如果经常查询的记录数目少于表中总记 录数目的15%时，可以创建索引。这个比例并不绝对，它与 全表扫描速度成反比。 3．对于大部分列值不重复的列可建立索引。 4．对于基数大的列，适合建立B树索引，而对于基数小的 列适合建立位图索引。 5．对于列中有许多空值，但经常查询所有的非空值记录的 列，应该建立索引。
3. 位图索引
当创建位图索引时，Oracle会扫描整张表，并为索引列 的每个取值建立一个位图。在这个位图中，对表中每一行 使用一位（bit，取值为0或1）来表示该行是否包含该位图 的索引列的取值，如果为1，则表示该位对应的ROWID所在 的记录包含该位图索引列值。最后通过位图索引中的映射 函数完成位到行的ROWID的转换。
字符类
LONG:可变长的字符串数据，最长2G，LONG具有VARCHAR2列 的特性，可以存储长文本一个表中最多一个LONG列
二进制类
LONG RAW: 可变长二进制数据，最长2G
LONG使用说明： 1、LONG 数据类型中存储的是可变长字符串，最大长度 限制是2GB。 2、对于超出一定长度的文本，基本只能用LONG类型来存储， 数据字典中很多对象的定义就是用LONG来存储的。 3、LONG类型主要用于不需要作字符串搜索的长串数据，如 果要进行字符搜索就要用varchar2类型。 4、很多工具，包括SQL*Plus，处理LONG 数据类型都是很 困难的。 5、LONG 数据类型的使用中，要受限于磁盘的大小。
创建索引 6.2 创建索引
其中： UNIQUE：表示唯一索引，默认情况下，不使用该选项。 BITMAP：表示创建位图索引，默认情况下，不使用该选项。 PCTFREE：指定索引在数据块中的空闲空间。对于经常插入 数据的表，应该为表中索引指定一个较大的空闲空间。 NOLOGGING：表示在创建索引的过程中不产生任何重做日志 信息。默认情况下，不使用该选项。 ONLINE：表示在创建或重建索引时，允许对表进行DML操作。 默认情况下，不使用该选项。 NOSORT：默认情况下，不使用该选项。则Oracle在创建索 引时对表中记录进行排序。如果表中数据已经是按该索引 顺序排列的，则可以使用该选项。
一、索引的概念
索引一旦建立后，当在表上进行DML操作时，Oracle会 自动维护索引，并决定何时使用索引。 索引的使用对用户是透明的，用户不需要在执行SQL语 句时指定使用哪个索引及如何使用索引，也就是说，无论 表上是否创建有索引，SQL语句的用法不变。用户在进行操 作时，不需要考虑索引的存在，索引只与系统性能相关。
4. 函数索引
函数索引既可以使用B树索引，也可以使用位图索引，可 以根据函数或表达式的结果的基数大小来进行选择，当函 数或表达式的结果不确定时采用B树索引，当函数或表达式 的结果是固定的几个值时采用位图索引。
4. 函数索引
下面通过一个例子看看函数索引的用法。在SALES表中， TOPIC列的值如果采用首字母大写的方式存储。
4. 函数索引
现在使用下列代码查询： SELECT * FROM SALES WHERE TOPIC=’TEE’; 将没有结果。现在忽略大小写，将代码修改如下： SELECT * FROM SALES WHERE UPPER(TOPIC)=’TEE’; 这样可以查到相应的结果，但是，由于不是直接查询TOPIC 列，所以，即使在TOPIC列上创建了索引也无法使用。
第 6章 Oracle索引 Oracle索引
Oracle9i关系数据库
本章内容
6.1 索引概述 6.2 创建索引 6.3 修改索引 6.4 查看索引
6.1 Oracle索引概述 索引概述
一、索引的概念
索引是建立在表上的可选对象，设计索引的目的是为 了提高查询的速度。但同时索引也会增加系统的负担，进 行影响系统的性能。 目录可以帮助读者快速查找所需要的内容，数据库中 的索引就类似于书的目录。有了索引，DML操作就能快速找 到表中的数据，而不需要扫描整张表。因此，对于包含大 量数据的表来说，设计索引，可以大大提高操作效率。在 书中，目录是内容和页码的清单，而在数据库中，索引是 数据和存储位置的列表。
能够操作 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、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。
3. 位图索引
4. 函数索引
前面的索引都是直接对表中的列创建索引，除此之外， Oracle还可以对包含有列的函数或表达式创建索引，这就 是函数索引。 当需要经常访问一些函数或表达式时，可以将其存储在 索引中，当下次访问时，由于该值已经计算出来了，因此， 可以大大提高那些在WHERE子句中包含该函数或表达式的查 询操作的速度。
1. 单列索引和复合索引
一个索引可以由一个或多个列组成。 基于单个列所创建的索引称为单列索引，基于两列或多 列所创建的索引称为多列索引。
B树索引 2. B树索引
B树索引是Oracle数据库中最常用的一种索引。当使用 CREATE INDEX语句创建索引时，默认创建的索引就是B树索 引。 B树索引是按B树结构或使用B树算法组织并存储索引数 据的。B树索引就是一棵二叉树，它由根、分支节点和叶子 节点三部分构成。其中，根包含指向分支节点的信息，分 支节点包含指向下级分支节点和指向叶子节点的信息，叶 子节点包含索引列和指向表中每个匹配行的ROWID值。叶子 节点是一个双向链表，因此可以对其进行任何方面的范围 扫描。
创建索引 6.2 创建索引
CREATE 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];
4. 函数索引
这时，就可以使用函数索引，创建函数索引的代码如下： CREATE INDEX funidx_upper_topic ON SALES(UPPER(TOPIC)); 由于函数索引存储了预先计算过的值，因此，查询时不需 要对每条记录都再计算一次WHERE条件，从而可以提高查询 的速度。 在函数索引中可以使用各种算术运算符、PL/SQL函数和内 置SQL函数，如LEN、TRIM、SUBSTR等。这些函数的共同特 点是为每行返回独立的结果，因此，象集函数（如SUM、 MAX、MIN、AVG等）不能使用。
பைடு நூலகம்
四、管理索引的原则
6．LONG和LONG RAW列不能创建索引。 7．经常进行连接查询的列上应该创建索引。 8．在使用CREATE INDEX语句创建查询时，将最常查询的列 放在其他列前面。 9．维护索引需要开销，特别时对表进行插入和删除操作时， 因此要限制表中索引的数量。对于主要用于读的表，则索 引多就有好处，但是，一个表如果经常被更改，则索引应 少点。 10．在表中插入数据后创建索引。如果在装载数据之前创 建了索引，那么当插入每行时，Oracle都必须更改每个索 引。
四、管理索引的原则
使用索引的目的是为了提高系统的效率，但同时它也 会增加系统的负担，进行影响系统的性能，因为系统必须 在进行DML操作后维护索引数据。 在新的SQL标准中并不推荐使用索引，而是建议在创建 表的时候用主键替代。因此，为了防止使用索引后反而降 低系统的性能，应该遵循一些基本的原则。
四、管理索引的原则
B树索引 2. B树索引
B树索引中所有叶子节点都具有相同的深度，所以不管 查询条件如何，查询速度基本相同。另外，B树索引能够适 应各种查询条件，包括精确查询、模糊查询和比较查询。
B树索引 2. B树索引
B树索引的分类如下所示。
– Unique：唯一索引，其索引值不能重复，但允许为NULL。在 创建索引时指定UNIQUE关键字可以创建唯一索引。当建立 “主键约束条件”时Oracle会自动在相应列上建立唯一索引， 主键列不允许为NULL。 – Non-Unique：非唯一索引，其索引值可以重复，允许为NULL。 默认情况下，Oracle创建的索引是非唯一索引。 – Reverse Key：反向关键字索引。通过在创建索引时指定 “REVERSE”关键字，可以创建反向关键字索引，被索引的每 个数据列中的数据都是反向存储的，但仍然保持原来数据列 的次序。
SQL*Plus 中操作LONG类型列： 1、set long n 2、col 列名 format An n代表n位字符（n为大于零的整数） An表示将此列的数据显示宽度限制为不超过n位。
创建索引 6.2 创建索引
创建索引使用CREATE INDEX语句。 在用户自己的方案中创建索引，需要CREATE INDEX系 统权限，在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外，索引需要存储空间，因此，还必须 在保存索引的表空间中有配额，或者具有UNLIMITED TABLESPACE系统权限。
3. 位图索引
在B树索引中，保存的是经排序过的索引列及其对应的 ROWID值。但是对于一些基数很小的列来说，这样做并不能 显著提高查询的速度。所谓基数，是指某个列可能拥有的 不重复值的个数。比如性别列的基数为2（只有男和女）。 因此，对于象性别、婚姻状况、政治面貌等只具有几 个固定值的字段而言，如果要建立索引，应该建立位图索 引，而不是默认的B树索引。
二、索引的原理
当在一个没有创建索引的表中查询符合某个条件的记 录时，DBMS会顺序地逐条读取每个记录与查询条件进行匹 配，这种方式称为全表扫描。 全表扫描方式需要遍历整个表，效率很低。
二、索引的原理
•假设SALES表的数据如表所示。
•ROWID伪列表示记录的物理存储位置。SALES表的TOPIC列 没有特定的顺序。 •现在查询TOPIC为PEN 的记录。由于在TOPIC列上没有索引， 该语句会搜索所有的记录。因为即使找到了PEN也不能保证 表中只有一个PEN，必须全部搜索一遍。
一、索引的概念
索引是建立在表上的可选对象。 索引的关键在于通过一组排序后的索引键来取代默认 的全表扫描检索方式，从而提高检索效率。 索引在逻辑上和物理上都与相关的表的数据无关，当 创建或删除一个索引时，不会影响基本的表、数据库应用 或其他索引，当插入、更改和删除相关的表记录时， Oracle会自动管理索引，如果删除索引，所有的应用仍然 可以继续工作。因此，在表上创建索引不会对表的使用产 生任何影响，但是，在表中的一列或多列上创建索引可以 为数据的检索提供快捷的存取路径，提高检索速度。
二、索引的原理
•在TOPIC列上建立索引，Oracle对全表进行一次搜索，将 每条记录的TOPIC值按升序排列，然后构建索引条目，即 （TOPIC值，ROWID值），存储到索引段中。
•当查询PEN的记录时，如何查找？
三、索引的类型
Oracle支持多种类型的索引，可以按列的多少、索引 值是否唯一和索引数据的组织形式对索引进行分类，以满 足各种表和查询条件的要求。 1．单列索引和复合索引 2．B树索引 3．位图索引 4．函数索引
3. 位图索引
当创建位图索引时，Oracle会扫描整张表，并为索引列 的每个取值建立一个位图。在这个位图中，对表中每一行 使用一位（bit，取值为0或1）来表示该行是否包含该位图 的索引列的取值，如果为1，则表示该位对应的ROWID所在 的记录包含该位图索引列值。最后通过位图索引中的映射 函数完成位到行的ROWID的转换。
字符类
LONG:可变长的字符串数据，最长2G，LONG具有VARCHAR2列 的特性，可以存储长文本一个表中最多一个LONG列
二进制类
LONG RAW: 可变长二进制数据，最长2G
LONG使用说明： 1、LONG 数据类型中存储的是可变长字符串，最大长度 限制是2GB。 2、对于超出一定长度的文本，基本只能用LONG类型来存储， 数据字典中很多对象的定义就是用LONG来存储的。 3、LONG类型主要用于不需要作字符串搜索的长串数据，如 果要进行字符搜索就要用varchar2类型。 4、很多工具，包括SQL*Plus，处理LONG 数据类型都是很 困难的。 5、LONG 数据类型的使用中，要受限于磁盘的大小。
创建索引 6.2 创建索引
其中： UNIQUE：表示唯一索引，默认情况下，不使用该选项。 BITMAP：表示创建位图索引，默认情况下，不使用该选项。 PCTFREE：指定索引在数据块中的空闲空间。对于经常插入 数据的表，应该为表中索引指定一个较大的空闲空间。 NOLOGGING：表示在创建索引的过程中不产生任何重做日志 信息。默认情况下，不使用该选项。 ONLINE：表示在创建或重建索引时，允许对表进行DML操作。 默认情况下，不使用该选项。 NOSORT：默认情况下，不使用该选项。则Oracle在创建索 引时对表中记录进行排序。如果表中数据已经是按该索引 顺序排列的，则可以使用该选项。
一、索引的概念
索引一旦建立后，当在表上进行DML操作时，Oracle会 自动维护索引，并决定何时使用索引。 索引的使用对用户是透明的，用户不需要在执行SQL语 句时指定使用哪个索引及如何使用索引，也就是说，无论 表上是否创建有索引，SQL语句的用法不变。用户在进行操 作时，不需要考虑索引的存在，索引只与系统性能相关。
4. 函数索引
函数索引既可以使用B树索引，也可以使用位图索引，可 以根据函数或表达式的结果的基数大小来进行选择，当函 数或表达式的结果不确定时采用B树索引，当函数或表达式 的结果是固定的几个值时采用位图索引。
4. 函数索引
下面通过一个例子看看函数索引的用法。在SALES表中， TOPIC列的值如果采用首字母大写的方式存储。
4. 函数索引
现在使用下列代码查询： SELECT * FROM SALES WHERE TOPIC=’TEE’; 将没有结果。现在忽略大小写，将代码修改如下： SELECT * FROM SALES WHERE UPPER(TOPIC)=’TEE’; 这样可以查到相应的结果，但是，由于不是直接查询TOPIC 列，所以，即使在TOPIC列上创建了索引也无法使用。
第 6章 Oracle索引 Oracle索引
Oracle9i关系数据库
本章内容
6.1 索引概述 6.2 创建索引 6.3 修改索引 6.4 查看索引
6.1 Oracle索引概述 索引概述
一、索引的概念
索引是建立在表上的可选对象，设计索引的目的是为 了提高查询的速度。但同时索引也会增加系统的负担，进 行影响系统的性能。 目录可以帮助读者快速查找所需要的内容，数据库中 的索引就类似于书的目录。有了索引，DML操作就能快速找 到表中的数据，而不需要扫描整张表。因此，对于包含大 量数据的表来说，设计索引，可以大大提高操作效率。在 书中，目录是内容和页码的清单，而在数据库中，索引是 数据和存储位置的列表。
能够操作 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、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。
3. 位图索引
4. 函数索引
前面的索引都是直接对表中的列创建索引，除此之外， Oracle还可以对包含有列的函数或表达式创建索引，这就 是函数索引。 当需要经常访问一些函数或表达式时，可以将其存储在 索引中，当下次访问时，由于该值已经计算出来了，因此， 可以大大提高那些在WHERE子句中包含该函数或表达式的查 询操作的速度。
1. 单列索引和复合索引
一个索引可以由一个或多个列组成。 基于单个列所创建的索引称为单列索引，基于两列或多 列所创建的索引称为多列索引。
B树索引 2. B树索引
B树索引是Oracle数据库中最常用的一种索引。当使用 CREATE INDEX语句创建索引时，默认创建的索引就是B树索 引。 B树索引是按B树结构或使用B树算法组织并存储索引数 据的。B树索引就是一棵二叉树，它由根、分支节点和叶子 节点三部分构成。其中，根包含指向分支节点的信息，分 支节点包含指向下级分支节点和指向叶子节点的信息，叶 子节点包含索引列和指向表中每个匹配行的ROWID值。叶子 节点是一个双向链表，因此可以对其进行任何方面的范围 扫描。
创建索引 6.2 创建索引
CREATE 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];
4. 函数索引
这时，就可以使用函数索引，创建函数索引的代码如下： CREATE INDEX funidx_upper_topic ON SALES(UPPER(TOPIC)); 由于函数索引存储了预先计算过的值，因此，查询时不需 要对每条记录都再计算一次WHERE条件，从而可以提高查询 的速度。 在函数索引中可以使用各种算术运算符、PL/SQL函数和内 置SQL函数，如LEN、TRIM、SUBSTR等。这些函数的共同特 点是为每行返回独立的结果，因此，象集函数（如SUM、 MAX、MIN、AVG等）不能使用。
பைடு நூலகம்
四、管理索引的原则
6．LONG和LONG RAW列不能创建索引。 7．经常进行连接查询的列上应该创建索引。 8．在使用CREATE INDEX语句创建查询时，将最常查询的列 放在其他列前面。 9．维护索引需要开销，特别时对表进行插入和删除操作时， 因此要限制表中索引的数量。对于主要用于读的表，则索 引多就有好处，但是，一个表如果经常被更改，则索引应 少点。 10．在表中插入数据后创建索引。如果在装载数据之前创 建了索引，那么当插入每行时，Oracle都必须更改每个索 引。
四、管理索引的原则
使用索引的目的是为了提高系统的效率，但同时它也 会增加系统的负担，进行影响系统的性能，因为系统必须 在进行DML操作后维护索引数据。 在新的SQL标准中并不推荐使用索引，而是建议在创建 表的时候用主键替代。因此，为了防止使用索引后反而降 低系统的性能，应该遵循一些基本的原则。
四、管理索引的原则
B树索引 2. B树索引
B树索引中所有叶子节点都具有相同的深度，所以不管 查询条件如何，查询速度基本相同。另外，B树索引能够适 应各种查询条件，包括精确查询、模糊查询和比较查询。
B树索引 2. B树索引
B树索引的分类如下所示。
– Unique：唯一索引，其索引值不能重复，但允许为NULL。在 创建索引时指定UNIQUE关键字可以创建唯一索引。当建立 “主键约束条件”时Oracle会自动在相应列上建立唯一索引， 主键列不允许为NULL。 – Non-Unique：非唯一索引，其索引值可以重复，允许为NULL。 默认情况下，Oracle创建的索引是非唯一索引。 – Reverse Key：反向关键字索引。通过在创建索引时指定 “REVERSE”关键字，可以创建反向关键字索引，被索引的每 个数据列中的数据都是反向存储的，但仍然保持原来数据列 的次序。
SQL*Plus 中操作LONG类型列： 1、set long n 2、col 列名 format An n代表n位字符（n为大于零的整数） An表示将此列的数据显示宽度限制为不超过n位。
创建索引 6.2 创建索引
创建索引使用CREATE INDEX语句。 在用户自己的方案中创建索引，需要CREATE INDEX系 统权限，在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外，索引需要存储空间，因此，还必须 在保存索引的表空间中有配额，或者具有UNLIMITED TABLESPACE系统权限。
3. 位图索引
在B树索引中，保存的是经排序过的索引列及其对应的 ROWID值。但是对于一些基数很小的列来说，这样做并不能 显著提高查询的速度。所谓基数，是指某个列可能拥有的 不重复值的个数。比如性别列的基数为2（只有男和女）。 因此，对于象性别、婚姻状况、政治面貌等只具有几 个固定值的字段而言，如果要建立索引，应该建立位图索 引，而不是默认的B树索引。
二、索引的原理
当在一个没有创建索引的表中查询符合某个条件的记 录时，DBMS会顺序地逐条读取每个记录与查询条件进行匹 配，这种方式称为全表扫描。 全表扫描方式需要遍历整个表，效率很低。
二、索引的原理
•假设SALES表的数据如表所示。
•ROWID伪列表示记录的物理存储位置。SALES表的TOPIC列 没有特定的顺序。 •现在查询TOPIC为PEN 的记录。由于在TOPIC列上没有索引， 该语句会搜索所有的记录。因为即使找到了PEN也不能保证 表中只有一个PEN，必须全部搜索一遍。
一、索引的概念
索引是建立在表上的可选对象。 索引的关键在于通过一组排序后的索引键来取代默认 的全表扫描检索方式，从而提高检索效率。 索引在逻辑上和物理上都与相关的表的数据无关，当 创建或删除一个索引时，不会影响基本的表、数据库应用 或其他索引，当插入、更改和删除相关的表记录时， Oracle会自动管理索引，如果删除索引，所有的应用仍然 可以继续工作。因此，在表上创建索引不会对表的使用产 生任何影响，但是，在表中的一列或多列上创建索引可以 为数据的检索提供快捷的存取路径，提高检索速度。
二、索引的原理
•在TOPIC列上建立索引，Oracle对全表进行一次搜索，将 每条记录的TOPIC值按升序排列，然后构建索引条目，即 （TOPIC值，ROWID值），存储到索引段中。
•当查询PEN的记录时，如何查找？
三、索引的类型
Oracle支持多种类型的索引，可以按列的多少、索引 值是否唯一和索引数据的组织形式对索引进行分类，以满 足各种表和查询条件的要求。 1．单列索引和复合索引 2．B树索引 3．位图索引 4．函数索引