Oracle 1Z031 第十二章 管理索引

1Z031 第十二章管理索引

目标

列举不同种类的索引和它们的用途

创建不同种类的索引

重建索引

删除索引

从数据字典中获取索引信息

察看索引的使用情况

 

Classification of Indexes

索引分类

逻辑上

       单个字段或者多个字段

       唯一或者非唯一

       基于函数

       域

物理上

       分区或者非分区

       B-Tree

       普通或者保留键值

       位图

 

       索引是一种树形结构容许直接访问表中的行。索引可以通过它们的逻辑设计或者他们物理实现来分类。逻辑分类把索引组织成为程序的透视，而物理分类按照索引存储的方式来区别。

 

Single Column and Concatenated Indexes

单字段和联结字段索引

一个单字段索引只有一个字段在索引键中——比如，一个在employees表中employees number字段上的索引。

一个联结索引，也可以叫做联合索引，是在一个表的多个字段上创建的索引。字段在联合索引中的顺序不需要和表中的字段的顺序一样，也不需要他们是毗邻的。

一个联合字段中最大的字段数目是32。但是，所以联合的字段的大小不能超过数据块大小的1/3。

 

Unique and Nonunique Indexes

唯一和非唯一字段

一个唯一索引保证这个字段在整个表中没有2行的数据是重复的。一个唯一索引的键值只能指向表中的一个行。

非唯一索引中，一个键值可以有多个行关联，可以用于保证非唯一。

 

 

Function-Based Indexes

基于函数的索引

一个基于函数的索引是通过使用函数或者表达式关联表中一个或者多个字段来创建的索引。一个基于函数的索引预计算函数或者表达式的值并把它存储在索引中。基于函数的索引可以创建为B-Tree或者位图索引。

       Domain Indexes

       域索引

       域索引是程序专用（TEXT，SPATIAL）索引，作为一个索引类型通过例程管理和访问。之所以叫做域索引是因为它的索引数据在程序专用区域。

       只有单行索引被域索引支持。你可以创建单行域索引在纯量，对象，或者LOB数据类型字段上。

       Partitioned and Nonpartitioned Indexes

       分区和非分区索引

       分区索引用于大的表在几个段中存储索引。分区容许一个索引分布到几个表空间，减少索引查找的竞争，增加可管理性。分区索引通常和分区表一起使用提高可扩展性和可管理性。每个表分区都可以创建一个分区索引。

       本章只讨论非分区的B-Tree索引和位图索引的创建和维护。

 

B-Tree Index

B-Tree 索引

 

索引如何存储

虽然所有的索引使用B-Tree结构，B-Tree 索引的终端通常和一个索引相连存储一个每个键值的ROWID的列表。

Structure of a B-Tree Index

B-Tree索引的结构

       在索引的顶端是一个根，保存一个实体指向下一个级别的索引。在下一级别中是分支数据块，顺序的指向下一个级别索引的数据块。在最低级别上是叶节点，包含有索引的实体指向表中的行。叶数据块都是双向连接以方便升序和降序扫描索引。

Format of Index Leaf Entries

索引叶实体的格式

一个索引实体是由下面的组件构成：

       一个实体头部，存储字段的数量和锁的信息

       键字段长度值对，把字段的长度定义在键值中跟随在字段的值后（这样的对是索引中数量中最大的）

       行的ROWID，包含键值的值

Index Leaf Entry Characteristics

索引叶实体的特征

       在非分区表上的B-Tree索引

       在多行中有同样键值的情况下，键值重复

       在索引键字段为NULL的情况下没有索引实体。因为在WHERE子句中标明NULL总是通过表扫描。

       限制的ROWID用于指向表中的行，因为所有的行属于同一个段。

Effect of DML Operations on an Index

DML对索引的操作的影响

Oracle 服务器维护所有的索引当DML操作申明在相应的表上。这里是DML命令对索引的影响：

       INSERT 操作的结果是在相应数据块中插入索引实体。

       删除一个行的结果只是在逻辑上删除索引实体。删除了的行的空间对新的实体并不可用直到索引块中的所有实体被删除。

       对键字段的更新的结果是逻辑上删除并对索引一个插入操作。PCTFREE 对索引没有效果除了在创建的时候。一个新的实体可能增加到一个索引数据块甚至在这个块的空间小于PCTFREE标明的值。

 

Bitmap Index

位图索引

位图索引在一些特定的情况下比B-Tree索引有优势：

       当一个表中有上百万行并且键字段有第的集容量——也就是行中只有少数的不同的值。例如位图索引比B-Tree索引更适合包含护照记录的表中性别和婚姻状况的字段。

       当查询中经常在WHERE条件中包含OR操作

       当键字段中只有可读或者低更新状况

 

Structure of a Bitmap Index

位图索引的结构

位图索引也是以B-Tree组织的，但是叶结点以位图存储每一个键值而不是ROWID的列表。位图中的每个设置了的BIT对于与一个可能的ROWID，这意味着有相关ROWID的行包含相关的键值。

       位图索引中的叶结点包含下述情况：

       一个实体头部，包含字段的数量和锁信息

       每个键值字段的长度和值的对（在这个例子中，键值只有一个字段，第一个实体的键值是Blue）

       开始的ROWID，在这个例子中包含文件号码3，数据块号码10，和行号码0

       结束ROWID 在这个例子中包括块号码 12 和行号码8

       位图段包括一系列的bit（bit在相关的行包含键值时设置，而在相关行不包含键值时不设置。Oracle 服务器使用专利的比较技术来存储位图段）

开始ROWID 是位图中位图段指向的第一个行——也就是说，位图中的第一个bit管理这个ROWID，位图中的第二个bit关联这个数据块中的下一个行，而结束ROWID 是一个指向表中由位图段覆盖的最后一行。位图索引使用限制ROWID。

 

Using a Bitmap Index

使用位图索引

B-Tree用于定位包含位图段中给定键的值的叶结点。开始ROWID 和位图段用于定位包含键值的行。

当表中键字段改变时，位图必须修改。这个结果在相关位图段中锁定。因为锁在整个位图段中需要，因此被位图覆盖的行在第一个事务结束前不能被其他的事物修改。

 

Comparing B-Tree and Bitmap Indexes

比较B-Tree 和位图索引

B-Tree                           位图

适合高分散的字段                 适合低分散的字段

对键值的更新相对不麻烦           对键值字段的更新非常麻烦

查询中用OR操作效果比较低       查询中用OR操作效果好

对OLTP很有用                   对数据仓库很有用

 

Creating Normal B-Tree Indexes

创建普通的B-Tree 索引

CREATE INDEX hr.employees_last_name_idx

ON hr.employees(last_name)

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx;

 

索引既可以由表的所有者来创建也可以由其他的用户创建，其他用户创建的和用表的所有者创建的是一样的。

几个语法参数：

UNIQUE          is used to specify a unique index(Nonunique is the default.)

schema          is the owner of the index/table

index           is the name of the index

table           is the name of the table

column          is the name of the column

ASC/ DESC       indicates whether the index should be createdin ascending or decending order.

TABLESPACE      identifies the tablespace where the index will be created

PCTFREE          is the amount of space reserved in each block (in percentage of total space minus the block header) at the time of creation for

accommodating new index entries

INITRANS        specifies the number of transaction entries preallocated in each block (The default and the minimum value is 2.)

MAXTRANS        limits the number of transaction entries that can be allocated to each block (The default is 255.)

STORAGE clause identifies the storage clause that determines how extents are allocated to the index

LOGGING          specifies that the creation of the index and subsequent operations on the index are logged in the redo log file

(This is the default.)

NOLOGGING       specifies that the creation and certain types of data loads are not logged in the redo log file NOSORT specifies that the rows are stored in the database in ascending order, and therefore, the Oracle server does not have to sort the rows while creating the index

• If MINIMUM EXTENT has been defined for the tablespace, the extent sizes for the

index are rounded up to the next higher multiple of the MINIMUM EXTENT value.

• If the [NO]LOGGING clause is omitted, the logging attribute of the index defaults to

the logging attribute of the tablespace in which it resides.

• PCTUSED cannot be specified for an index. Because index entries must be stored in the correct order, the user cannot control when an index block is used for inserts.

• If the NOSORT keyword is used when the data is not sorted on the key, the statement

terminates with an error. This option is likely to fail if the table has had several DML

operations on it.

• The Oracle server uses existing indexes to create a new index, if possible. This happens when the key for the new index corresponds to the leading part of the key of an existing index.

 

Creating Indexes: Guidelines

创建索引：概要

平衡查询和DML的需要

放置在单独的表空间中

使用统一的扩展大小：5个数据块的倍数或者表空间MINIMUM EXTENT 大小的倍数。

对大的索引考虑使用NOLOGGING

索引的INITRANS 应该比相关的表的要大

Indexes and PCTFREE

PCTFREE 参数对索引和表的工作方式是不一样的。索引实体不需要更新只有插入，更新总是导致一个逻辑上的删除和插入擦作。

对只是单调的增加的字段索引使用低的PCTFREE值，而对于那些值可能取任何值的字段，新的值可能落在当前值范围内——你应该使用高PCTFREE。对于高PCTFREE参数情况可以使用下述公式来计算：

       （最大数量的行—初始行数量*100）/ 最大的行数量

最大值可以通过时间段来统计，比如年。

 

Creating Bitmap Indexes

创建位图索引

用CREATE_BITMAP_AREA_SIZE来标明分配给位图创建的内存空间

CREATE BITMAP INDEX orders_region_id_idx

ON orders(region_id)

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx;

 

语法

CREATE BITMAP INDEX [schema.] index

ON [schema.] table

(column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...)

[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ storage-clause ]

[ LOGGING| NOLOGGING ]

[ NOSORT ]

注意位图索引不可以是唯一的

 

Changing Storage Parameters for Indexes

改变位图索引的存储参数

ALTER INDEX employees_last_name_idx

STORAGE(NEXT 400K

MAXEXTENTS 100);

 

 

Allocating and Deallocating Index Space

ALTER INDEX orders_region_id_idx

ALLOCATE EXTENT (SIZE 200K

DATAFILE ‘/DISK6/indx01.dbf’);

 

 

ALTER INDEX orders_id_idx

DEALLOCATE UNUSED;

用DEALLOCATE 来释放索引中在高水位线上的未使用空间。

 

Rebuilding Indexes

重建索引

用ALTER INDEX 命令来

       把索引移到不同的表空间

       通过移除删除的实体来提高空间利用

       把一个反向键值的索引改为通常的B-Tree索引或者反过来

ALTER INDEX orders_region_id_idx REBUILD

TABLESPACE indx02;

重建有下述特征：

       新的索引使用已经存在的索引作为数据源

       在使用一个已经存在的索引建立索引的时候不需要排序，可以得到好的性能。

       新的索引建立后旧的索引删除，在重建过程中，需要大量的空间。

       新建立的所有不包含任何删除的实体。因此在空间使用上更有效。

       查询可以继续使用现存的索引当新的索引建立的时候

Possible Rebuild Situations

       当前索引需要移动到不同的表空间。

       一个索引包含很多的删除实体。这是典型的索引面对的问题。

       一个普通索引需要转换为一个反向键值索引。

       索引的表移动到另一个表空间alter table … … move tablespace

      

ALTER INDEX [schema.] index REBUILD

[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ storage-clause ]

[ LOGGING| NOLOGGING ]

[ REVERSE | NOREVERSE ]

 

Online Rebuild of Indexes

在线重建索引

可以重建索引的时候减少表锁

       ALTER INDEX orders_id_idx REBUILD ONLINE;

重建索引是一个耗费时间的任务，特别是表非常大的时候。在Oracle8i 前创建和重建一个索引需要一个DML锁以防止冲突的操作。

       Oracle8i 提供一个机制创建和重建一个索引的同时容许冲突的操作发生在这个表上，但是还是不建议运行大的DML操作。

       注意：这里还是有DML锁，这意味着你不能运行DDL操作在在线建立索引的时候。

       限制：

       你不可以在一个临时表上创建索引

       你不可以重建一个分区索引实体。你必须重建每一个分区或者子分区。

       你不可以同时释放没有使用的空间

       你不可以改变索引的PCTFREE参数

 

 

Coalescing Indexes

合并索引

ALTER INDEX orders_id_idx COALESCE;

当有索引分裂的时候你可以重建或者合并索引。在作之前你必须考虑每种操作的成本和效益并选择最合适当前情况的操作。合并索引是在线数据块的重建。

 

Checking Index Validity

检查索引有效性

 

ANALYZE INDEX orders_region_id_idx

VALIDATE STRUCTURE;

 

分析索引适合下述情况

       检查所有的索引数据块的错误，注意这个命令并不是检查索引是否合表中的数据匹配

       在INDEX_STATS视图中产生索引相关的信息

       运行命令后察看INDEX_STATS视图获取信息

 

SELECT blocks, pct_used, distinct_keys

lf_rows, del_lf_rows

FROM index_stats;

BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS

------ --------- -------- ------------

25      11          14         0

1 row selected.

 

重新组织这个索引如果它有大量的删除行比如:DEL_LF_ROWS 比 LF_ROWS超过了30%。

 

Dropping Indexes

 

在大批量导入的时候删除并重新创建一个索引

删除不经常用的索引，在需要的时候重建。

删除并重建失效的索引

 

Identifying Unused Indexes

标明不用的索引

 

开启查看索引的使用情况

 

ALTER INDEX summit.orders_id_idx

MONITORING USAGE

 

停止查看索引的使用

 

ALTER INDEX summit.orders_id_idx

NOMONITORING USAGE

从9i开始，索引的使用情况可以在V$OBJECT USAGE中收集和现实。如果收集的信息表明一个索引从未使用过，这个索引可以删除掉。消除为使用的索引，减少了Oracle DML上必须的开销从而提高了性能。每次monitoring usage使用的时候，v$object_useage将重置。

V$OBJECT_USAGE Columns

INDEX_NAME: The index name.

TABLE_NAME: The corresponding table.

MONITORING: Indicates whether monitoring is ON or OFF.

USED: Indicates YES or NO whether index has been used during the monitoring time.

START_MONITORING: Time monitoring began on index.

END_MONITORING: Time monitoring stopped on index.

 

Obtaining Index Information

获取索引信息

可以通过查询数据字典获取索引的信息

DBA_INDEXES:提供索引上的信息。

DBA_IND_COLUMNS:

提供索引字段的信息

DBA_IND_EXPRESSIONS

提供基于函数的索引信息

V$OBJECT_USAGE:

提供索引使用情况的信息