Oracle管理索引

1、12Copyright Oracle Corporation, 2001. All rights reserved.离离DEADLINE还有还有35天天12Copyright Oracle Corporation, 2001. All rights reserved.管理索引管理索引 12-3Copyright Oracle Corporation, 2001. All rights reserved.目标目标 完成这一课的学习后，您应该能达到下列目标：完成这一课的学习后，您应该能达到下列目标： 列出各种类型的索引及其用途列出各种类型的索引及其用途 创建各种类型的索引创建各种类型的索引 重新组

2、织索引重新组织索引 维护索引维护索引 监视索引的使用监视索引的使用 12-4Copyright Oracle Corporation, 2001. All rights reserved.索引分类索引分类 逻辑逻辑 单列或串接单列或串接 唯一或非唯一唯一或非唯一 基于函数基于函数 物理物理 分区或非分区分区或非分区 B 树树 正常或反向键正常或反向键 位图位图 12-5Copyright Oracle Corporation, 2001. All rights reserved.12-6Copyright Oracle Corporation, 2001. All rights reserve

3、d.B 树索引树索引 索引项标题索引项标题 键列长度键列长度 键列值键列值 行标识行标识 根根 分支分支 叶叶 索引项索引项 12-7Copyright Oracle Corporation, 2001. All rights reserved.B 树索引树索引 索引叶项的特征：索引叶项的特征： 在非分区表上的在非分区表上的 B 树索引中：树索引中： 如果多行具有相同的键值，除非对索引进行了压缩，否则键值重复。如果多行具有相同的键值，除非对索引进行了压缩，否则键值重复。 对于所有键列都为对于所有键列都为 NULL 的行，没有对应的索引项。因此，的行，没有对应的索引项。因此， WHERE 子子句

4、中索引所在的列值为句中索引所在的列值为 NULL，则，则始终进行全表扫描。始终进行全表扫描。 因为所有行都属于同一段，所以使用受限行标识指向表中的行。因为所有行都属于同一段，所以使用受限行标识指向表中的行。 DML 操作对索引的影响：操作对索引的影响： 在表上执行在表上执行 DML 操作时，操作时，Oracle 服务器将维护所有的索引。下面解释服务器将维护所有的索引。下面解释 DML 命令对索引的影响：命令对索引的影响： 插入操作导致在对应的索引块中插入索引项。插入操作导致在对应的索引块中插入索引项。 删除行只导致逻辑删除索引项。删除的行所用的空间仍不能用于新项，删除行只导致逻辑删除索引项。删

5、除的行所用的空间仍不能用于新项，直到删除块中的所有项。直到删除块中的所有项。 更新键列将导致逻辑删除和向索引插入项。更新键列将导致逻辑删除和向索引插入项。12-8Copyright Oracle Corporation, 2001. All rights reserved.位图索引位图索引 关键字关键字 开始开始 行标识行标识 结束结束 行标识行标识 位图位图 表表 索引索引 块块 10块块 11块块 12文件文件 312-9Copyright Oracle Corporation, 2001. All rights reserved.位图索引的结构位图索引的结构 位图索引也是一种位图索引也是

6、一种 B 树结构，但叶节点存储每个键值的位图而非树结构，但叶节点存储每个键值的位图而非行标识列表。位图中的每一位对应一个可能的行标识，如果被置行标识列表。位图中的每一位对应一个可能的行标识，如果被置位，则意味着具有相应行标识的行包含键值。位，则意味着具有相应行标识的行包含键值。 如图所示，位图索引的叶节点包含下列几项：如图所示，位图索引的叶节点包含下列几项： 项标题，包含行数和锁的信息项标题，包含行数和锁的信息 键值由每个键列键值由每个键列的的 “长度长度 - 值值” 双值组成（本例中，关键字仅包双值组成（本例中，关键字仅包含一列，第一项的键值为含一列，第一项的键值为 “学士学士”）。）。 开

7、始行标识，本例中的开始行标识包含文件号开始行标识，本例中的开始行标识包含文件号 3、块号、块号 10 和行号和行号 0 结束行标识，本例中的结束行标识包含文件号结束行标识，本例中的结束行标识包含文件号 3、块号、块号 12 和行号和行号 8 位图段，由位串组成（对应的行包含键值时设置位；不包含键值时位图段，由位串组成（对应的行包含键值时设置位；不包含键值时不设置位。不设置位。Oracle 服务器使用专利压缩技术存储位图段。）服务器使用专利压缩技术存储位图段。）12-10Copyright Oracle Corporation, 2001. All rights reserved.12-11Co

8、pyright Oracle Corporation, 2001. All rights reserved.比较比较 B 树索引和树索引和 位图索引位图索引 B 树树 适用于高基数列适用于高基数列 更新关键字的成本相对较低更新关键字的成本相对较低 使用使用 OR 谓词进行查询时谓词进行查询时效率较低效率较低 对对 OLTP 很有用很有用 位图位图 适用于低基数列适用于低基数列 更新键列的成本非常高更新键列的成本非常高 使用使用 OR 谓词进行查询时谓词进行查询时效率较高效率较高 对数据仓库很有用对数据仓库很有用 12-12Copyright Oracle Corporation, 2001.

9、All rights reserved.创建索引：原则创建索引：原则 平衡查询和平衡查询和 DML 存放在单独的表空间中存放在单独的表空间中 使用统一的区大小：块数是使用统一的区大小：块数是 5 的倍数或对表空间的倍数或对表空间使用使用 MINIMUM EXTENT 大小大小 对于大型索引，请考虑使用对于大型索引，请考虑使用 NOLOGGING 通常，通常，INITRANS 在索引中比在对应的表中高。在索引中比在对应的表中高。 12-13Copyright Oracle Corporation, 2001. All rights reserved.12-14Copyright Oracle C

10、orporation, 2001. All rights reserved.创建索引创建索引CREATE UNIQUE|BITMAP INDEX 用户名用户名.索引名索引名ON 用户名用户名.表名表名(列名列名 ASC|DESC ,列名列名 ASC|DESC ) TABLESPACE 表空间名表空间名 PCTFREE 正整数值正整数值 INITRANS 正整数值正整数值 MAXTRANS 正整数值正整数值 存储子句存储子句 LOGGING | NOLOGGING NOSORT 12-15Copyright Oracle Corporation, 2001. All rights reserve

11、d.创建索引创建索引 PCTFREE：创建索引时为容纳新的索引项而在每块中保留的空间大小创建索引时为容纳新的索引项而在每块中保留的空间大小 INITRANS：指定每块中预先分配的事务处理项的数目（缺省值和最指定每块中预先分配的事务处理项的数目（缺省值和最小值为小值为 2。）。） MAXTRANS：限制可以为每个块分配的事务处理项数（缺省值为限制可以为每个块分配的事务处理项数（缺省值为 255。）。） STORAGE 子句：标识确定如何为索引分配区的存储子句子句：标识确定如何为索引分配区的存储子句 LOGGING：指定在重做日志文件中记录索引创建操作和在索引上执行指定在重做日志文件中记录索引创建

12、操作和在索引上执行的后续操作（这是缺省值。）的后续操作（这是缺省值。） NOLOGGING：指定在重做日志文件中不记录创建操作和某些类型的指定在重做日志文件中不记录创建操作和某些类型的数据加载操作数据加载操作 NOSORT：指定将行按升序存储在数据库中，这样，指定将行按升序存储在数据库中，这样，Oracle 服务器在服务器在创建索引时不必对行进行排序创建索引时不必对行进行排序 12-16Copyright Oracle Corporation, 2001. All rights reserved.创建正常的创建正常的 B 树索引树索引 create index c_name on course

13、(cname) storage( initial 100k next 100k maxextents 100)tablespace test1;12-17Copyright Oracle Corporation, 2001. All rights reserved.12-18Copyright Oracle Corporation, 2001. All rights reserved.创建位图索引创建位图索引 create bitmap index c_name on course(cname) storage( initial 100k next 100k maxextents 100)ta

14、blespace test1;12-19Copyright Oracle Corporation, 2001. All rights reserved.12-20Copyright Oracle Corporation, 2001. All rights reserved.分配和回收索引空间分配和回收索引空间 ALTER INDEX schema.indexALLOCATE EXTENT (SIZE integer K|M DATAFILE filename )| DEALLOCATE UNUSED KEEP integer K|M 12-21Copyright Oracle Corporat

15、ion, 2001. All rights reserved.分配和回收索引空间分配和回收索引空间 ALTER INDEX c_nameALLOCATE EXTENT (SIZE 200KDATAFILE );ALTER INDEX c_nameDEALLOCATE UNUSED;12-22Copyright Oracle Corporation, 2001. All rights reserved.重建索引重建索引 ALTER INDEX schema. index REBUILD TABLESPACE tablespace PCTFREE integer INITRANS integer

16、MAXTRANS integer storage-clause LOGGING| NOLOGGING REVERSE | NOREVERSE ALTER INDEX .REBUILD 命令不能用于将位图索引更改为命令不能用于将位图索引更改为 B 树索引，反之亦然。树索引，反之亦然。只能为只能为 B 树索引指定树索引指定 REVERSE 或或 NOREVERSE 关键字。关键字。 12-23Copyright Oracle Corporation, 2001. All rights reserved.重建索引重建索引 使用使用 ALTER INDEX 命令执行以下操作：命令执行以下操作： 将索引

17、移到另一个表空间中将索引移到另一个表空间中 通过移除已删除的项，提高空间的使用率通过移除已删除的项，提高空间的使用率 ALTER INDEX orders_region_id_idx REBUILDTABLESPACE indx02;12-24Copyright Oracle Corporation, 2001. All rights reserved.12-25Copyright Oracle Corporation, 2001. All rights reserved.联机重建索引联机重建索引 可以使用最小限度的表锁定来重建索引。可以使用最小限度的表锁定来重建索引。 限制：限制： 不能在临

18、时表中重建索引不能在临时表中重建索引 不能重建整个分区索引。必须分别重建每个分区或子分区。不能重建整个分区索引。必须分别重建每个分区或子分区。 也不能回收未用空间。也不能回收未用空间。 不能整个更改索引的不能整个更改索引的 PCTFREE 参数值。参数值。 ALTER INDEX orders_id_idx REBUILD ONLINE;12-26Copyright Oracle Corporation, 2001. All rights reserved.合并索引合并索引 合并前合并前 合并后合并后 ALTER INDEX c_name COALESCE;12-27Copyright Ora

19、cle Corporation, 2001. All rights reserved.检查索引及其有效性检查索引及其有效性 ANALYZE INDEX c_nameVALIDATE STRUCTURE;INDEX_STATSSELECT blocks, pct_used, distinct_keys, lf_rows, del_lf_rows FROM index_stats;如果索引中已删除行的比例很高，请重新组织该索引。例如：当如果索引中已删除行的比例很高，请重新组织该索引。例如：当 DEL_LF_ROWS 占占 LF_ROWS 的比率超过的比率超过 30% 时时12-28Copyrigh

20、t Oracle Corporation, 2001. All rights reserved.12-29Copyright Oracle Corporation, 2001. All rights reserved.标识索引标识索引 要开始监视索引的使用，请执行以下语句：要开始监视索引的使用，请执行以下语句： 要停止监视索引的使用，请执行以下语句：要停止监视索引的使用，请执行以下语句： 查看索引使用情况查看索引使用情况ALTER INDEX c_name MONITORING USAGE;ALTER INDEX c_name NOMONITORING USAGE;SELECT * FROM V$OBJECT_USAGE;12-30Copyright Oracle Corporation, 2001. All rights reserved.删除索引删除索引 在批量加载前，请删除并重新创建索引。在批量加载前，请删除并重新创建索引。 删除不常用的索引，并在需要时创建这些索引。删除不常用的索引，并在需要时创建这些索引。 删除并重新创建无效的索引。删除并重新创建无效的索引。 DROP INDEX C_NAME;12-31Copyright Oracle Corporation, 2001. All rights reserved.12-32Copyright