首页 > 代码库 > [转]Oracle DB 索引

[转]Oracle DB 索引

2024-07-06 11:04:06   226人阅读

索引是数据库对象，可以通过创建索引来提高一些查询的性能。在你创建主键或唯一约束条件时，服务器会同时自动创建索引。
  • 索引
索引具有以下特点：
? 是一个方案对象
? Oracle Server 可用来通过指针加快行检索速度
? 可通过使用快速路径访问方法迅速找到数据来减少磁盘的输入/ 输出(I/O) 
? 与建立索引的表无关
? 由Oracle Server 自动使用和维护
 
Oracle Server  索引是一个方案对象，可以通过指针加快行检索速度。可以显式创建索引，也可以自动创建索引。如果没有在列上建立索引，则会对整个表进行扫描。
使用索引可以直接而快速的访问表中的行。其作用是通过使用索引路径快速找到数据来减少磁盘的I/O。索引由Oracle Server  自动使用和维护。创建索引之后，就不需要用户直接执行任何操作了。
索引是与表关联的可选结构。创建索引可提高更新和检索数据的性能。Oracle  索引提供了到数据行的直接访问路径。
可以对表的一个或多个列创建索引。创建索引后，Oracle  服务器会自动维护和使用索引。
表数据的更新（如添加新行、更新行或删除行）会自动传播到所有相关的索引，这些对用户来说是完全透明的。
索引在逻辑上和实际上都独立于建立索引的表。这意味着可以在任何时候创建或删除索引，而不会对基表或其它索引产生任何影响。
注：在删除表时，会另外删除相应的索引。
 
  • 索引类型
可以根据需要使用几种类型的索引结构：
? B 树索引采用二进制树的形式，它是默认的索引类型。
? 在位图索引中，每个建立索引的独特值都有一个位图，每一位的位置都表示一行，其中可能包含（也可能不包含）索引值。对于低基数列而言，这是最佳结构。
 
下面是最常见的索引形式：
?B 树
? 位图
B  树索引的键值存储在平衡树（B  树）中，以提供快速的二进制搜索。
在位图索引中，每个要建立索引的独特键值都有一个位图。在每一位图中，为要建立索引的表中的每一行都保留了一位。这样，独特值很少时可快速进行查找；也就是说，索引列的基数较低。性别指示器就是这种索引的一个示例。这种指示器只包含值“M ”和“F”，因此，只需要搜索两个位图。再举例说，如果对phone_number列使用位图索引，则需要管理和搜索太多的位图，这种情况下效率非常低。所以，请对低基数列使用位图索引。
  • B  树索引
B  树索引的结构
索引的顶层为根，它包含指向索引中下一层次的条目。下一层次为分支块，它又指向位于索引中下一层次的块。位于最低层次的是叶节点，它包含指向表行的索引条目。叶块在两个方向都是相互关联的，这便于按键值升序或降序扫描索引。
索引叶条目的格式
索引条目包括下列组成部分：
? 条目头，用于存储列数和锁定信息。
? 键列长度- 值对，用于定义键中的列大小，后面跟着列值（这种对的数目就是索引中的最大列数）。
? 包含键值的行的ROWID。
 
索引叶条目的特性
在非分区表的B  树索引中：
? 当多个行具有相同的键值时，如果不压缩索引，键值会出现重复。
? 当某行包含的所有键列为NULL 时，该行没有对应的索引条目。因此，当WHERE子句指定了NULL 时，始终会导致全表扫描。
? 因为所有行属于同一个段，所以要使用受限的ROWID指向表行。
对索引执行DML 操作的效果
对表执行DML 操作时，Oracle  服务器会维护所有索引。下面说明对索引执行DML 命令所产生的效果：
? 执行插入操作会导致在相应块中插入索引条目。
? 删除一行只会导致对索引条目进行逻辑删除。在删除块中的所有条目之前，已删除行占用的空间不可用于新条目。
? 更新键列会导致对索引进行逻辑删除和插入。PCTFREE设置对索引没有影响，但创建时除外。即使索引块的空间少于PCTFREE指定的空间，也可以向索引块添加新条目。
  • 位图索引
在以下特定情况下，位图索引比B  树索引更有优势：
? 表具有数百万行且键列的基数较低时，也就是列的独特值极少时。例如，对于护照记录表中的性别和婚姻状况列，位图索引可能比B  树索引更可取。
? 经常使用涉及OR运算符的多个WHERE条件组合进行查询时。
? 键列上存在只读活动或较少更新活动时。
位图索引的结构
位图索引也可以按B  树形式进行组织，但是，叶节点会存储每个键值的位图，而不是ROWID列表。位图中每一位都对应于一个可能的ROWID，如果已设置该位，则表示具有对应ROWID的行包含键值。
如图所示，位图索引的叶节点包含：
? 条目头，其中包含列数和锁定信息
? 由每个键列的长度－值对组成的键值。在本示例中，键只包含一列，第一个条目的键值为Blue 。
? 开始ROWID，在本示例中它指定块号10、行号0  和文件号3。
? 结束ROWID，在本示例中它指定块号12、行号8  和文件号3。
? 由位字符串组成的位图段。（如果对应行包含键值，则会设置位；如果对应行不包含键值，则不会设置位。Oracle  服务器使用已获专利的压缩技术存储位图段。）
开始ROWID是位图中位图段指向的第一行ROWID，就是说，位图的第一个位对应于该ROWID，位图的第二个位对应于块中的下一行。结束ROWID是一个指针，它是指向由位图段覆盖的表中的最后一行。位图索引使用受限的ROWID。
使用位图索引
B  树用于定位叶节点，这些节点包含指定键值的位图段。开始ROWID和位图段用于定位包含键值的行。
更改了表中的键列后，也必须更改位图。这会导致相关的位图段处于锁定状态。由于锁是在整个位图段上获得的，因此，在第一个事务处理结束之前，位图覆盖的行不能由其它事务处理进行更新。
 
  • 索引选项
? 唯一索引可确保每个索引值是唯一的。
? 索引可按升序或降序存储其键值。
? 反向键索引以反向顺序存储其键值字节。
? 组合索引是基于多列的索引。
? 基于函数的索引是以函数返回值为基础的索引。
? 在压缩的索引中重复的键值已被删除。
为了提高检索效率，在索引中按降序存储键可能更有益。这是根据最常用的数据访问方式得出的结论。
反向键索引会按反向顺序存储索引值的字节。这可以减少索引中特定热点的活动量。如果多个用户正按同一顺序处理数据，那么在任何给定时刻，（当前正在处理的）键值的前缀部分是很接近。因此，在索引结构的该区域中会发生大量的活动。为反向字节样式的键值建立索引后，反向键索引会将这些活动分布到整个索引结构中。
按多列组合创建的索引称为组合索引。例如，根据一个人的姓与名可创建一个索引：
CREATE INDEX name_ix ON employees 
(last_name, first_name); 
使用基于函数的索引可为函数返回值创建索引。这个函数可以是一个内置SQL 函数、外部提供的PL/SQL  函数，或用户编写的函数。这样一来，服务器根据索引表达式执行搜索时，不必为每个键值调用函数。以下示例为返回的树木材积建立索引，树木材积由函数根据每一种树的树种、高度和树围（这些是TREES表中的列）进行计算：
CREATE INDEX tree_vol_ix ON 
TREES(volume(species,height,circumference)); 
然后，WHERE子句中包含表达式volume(species,height,circumference) 的任何查询都能利用此索引，从而提高了执行速度，因为每一种树的材积已被计算出来。像普
通索引一样，基于函数的索引自动进行维护。
使用压缩索引可减少执行过程中占用的磁盘空间。由于删除了重复的键值，指定的磁盘空间量可以容纳更多的索引条目，因此，相同的时间段内可以从磁盘读取更多的条目。必须分别在写入索引和读取索引时执行压缩和解压缩。
  • 如何创建索引
? 自动创建：如果在表定义中定义了PRIMARY KEY 或UNIQUE 约束条件，则会自动创建一个唯一的索引。
? 手动创建：用户可以通过对列创建非唯一的索引来加快行访问速度。
 
可以创建两种类型的索引。
? 唯一索引：如果你在表中定义的列具有PRIMARY KEY 或UNIQUE 约束条件，则Oracle Server  会自动创建此类型的索引。索引的名称和约束条件的名称相同。
? 非唯一索引：这种类型的索引可以由用户创建。例如，可以通过在查询中为联接创建一个FOREIGN KEY 列索引来提高检索速度。
注：可以手动创建唯一索引，但是建议你创建唯一约束条件，这样可隐式创建唯一索引。
 
  • 创建索引
? 对一个或多个列创建索引：

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>

      ON <schema>.<table_name>

           (<column_name> | <expression> ASC | DESC,
            <column_name> | <expression> ASC | DESC,...)
     TABLESPACE <tablespace_name>
     STORAGE <storage_settings>
     LOGGING | NOLOGGING
    COMPUTE STATISTICS
     NOCOMPRESS | COMPRESS<nn>
     NOSORT | REVERSE
     PARTITION | GLOBAL PARTITION<partition_setting>

 

相关说明

1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4）STORAGE：可进一步设置表空间的存储参数
5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6）COMPUTE STATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

 

指定UNIQUE 时指出单个或多个列的列值（索引所基于的）必须为唯一。指定BITMAP时指出会使用位图为每个不同的键创建索引，而不用是分别为每一行建立索引。建立位图索引时会将与键值关联的rowids 存储为位图。

 

? 提高对EMPLOYEES 表中LAST_NAME 列的查询访问速度：
 
CREATE INDEX emp_last_name_idx    ON employees(last_name);
 
也可以在“Administration（管理）”页的“Schema（方案）”标题下单击“Indexes（索引）”链接，以查看“Indexes（索引）”页。可查看索引属性，或使用“Actions（操作）”菜单查看索引的相关性。
根据对表设置的约束条件，可显式或隐式建立索引。定义主键就是隐式建立索引的一个示例，此时为了对列强制实施唯一性，会自动建立唯一索引。
 
 
 
 
  • 索引创建准则
在以下情况下应创建索引：
列包含值的范围很广
列包含大量空值
在WHERE子句或联接条件中频繁使用一个或多个列
表很大，但是预计大多数查询要检索的行小于表中行数的2%  至4%
请勿在以下情况下创建索引：
这些列没有频繁用做查询中的条件
表比较小，或者预计大多数查询要检索的行超过表中行数的2%  至4%
表更新频繁
在表达式中已引用索引列
 
索引多未必会更好
并不是表索引越多查询速度越快。在具有索引的表上提交每项DML 操作后，都意味着必须更新相应的索引。与表关联的索引越多，在执行DML 操作之后，Oracle Server  为更新全部索引所做的工作就越多。
什么情况下要创建索引
因此，仅在以下情况下才应创建索引：
? 列包含值的范围很广
? 列包含大量的空值
? 在WHERE子句或联接条件中频繁使用一个或多个列
? 表很大，但是预计大多数查询要检索的行小于行数的2%  至4% 
注意，如果要强制实现唯一性，则应在表定义中定义一个唯一约束条件。此时可以自动创建一个唯一索引。
 
  • 删除索引
? 使用DROP INDEX命令可从数据字典中删除索引：
DROP INDEX index ;
? 从数据字典中删除emp_last_name_idx索引：
DROP INDEX em p_last_name_idx;
? 要删除索引，必须是索引的所有者或拥有DROP ANY INDEX权限。
 
 
不能修改索引。要更改索引，必须先删除它，然后重新创建。
发出DROP INDEX语句后，可从数据字典中删除索引定义。要删除索引，必须是索引的所有者或拥有DROP ANY INDEX 权限。
在此语法中，index是索引的名称。
注：如果删除了一个表，则会自动删除索引和约束条件，但会保留视图和序列。

 

来源：http://blog.csdn.net/rlhua/article/details/12780775


联系
我们