索引

PDF下载：http://www.java1234.com/a/javabook/database/2014/0711/2493.html

Oracle索引

索引是一种可以选择创建的数据库对象，它主要用于提高查询性能，数据库索引的用途与一本书的索引类似。书的索引把书的主题和页码进行关联，想在一本书中查找信息时，首先检查索引，从书中找到要查的主题，确定相关的页码，通常比直接翻书查找要快得多。有了索引提供的信息，就可以直接翻到这本书中的具体页码。如果某个主题只在书的几页内出现，那么读取的页面数量是很少的。采用这种方式，一个主题在书中出现的次数越多，索引对它产生的作用就越小。

与书的索引类似，数据库索引把用户感兴趣的列值连同其行标识符（ROWID）存储在一起。ROWID包含了存储列值的表行在磁盘上的物理位置。有了ROWID，Oracle可以通过最少量的磁盘读取，有效的检索表中的数据。采用这种方式，索引的功能就像表中数据的快捷方式，如果没有可用的索引，那么Oracle就必须读取表中的每一行，才能确定该行是否包含所需的信息。

除了提高性能，Oracle还使用索引来协助强制执行已启用的主键和唯一键约束。此外，当为主键列建立索引时，Oracle可以更好的管理表锁定的情况。

用索引提高性能

1. 索引是一种可选对象，它是在一个表的一个或多个列上定义的
2. 索引要消耗资源
3. B树索引是Oracle默认的索引类型
4. 在表中值最独特的列上创建B树索引的效率最高
5. 创建合适的索引能提高性能
6. 在某些情况下，查询优化器会选择不使用索引。换言之，查询优化器这时计算出全表扫描的成本低于使用索引时的成本
7. 在某些情况下，Oracle只需访问索引就可以检索出要查询的数据，而无需对表进行访问

确定使用哪种类型的索引

Oracle的索引和功能说明

索引类型	用途
B树	默认的索引类型，平衡树索引，适用于高基数（不同值程度高）的列，除非有特殊原因需要使用不同的索引类型或功能，否则用正常的B树索引即可
索引组织表	当主键包含大多数的列值时很有效率。访问这种索引就像访问表一样。数据存储在一个类似B树的结构中
唯一索引	B树索引的一种形式，用于强制执行列值的唯一性。经常与主键和唯一约束一起使用，但也可以独立于约束而创建
反向键索引	B树索引的一种形式，在索引有许多顺序插入的情况下，用于平衡I/O
键压缩索引	适用于前导列经常重复的组合索引，压缩叶块条目。此功能适用于B树索引或ITO（索引组织表）索引
降序索引	B树索引的一种形式，在索引对应的列值按降序（默认顺序是升序）排序时使用。反向键索引不能指定降序，如果是位图索引，那么Oracle忽略降序
位图索引	对于包含低基数列以及在SQL语句的WHERE字句中使用许多AND或OR运算符的数据仓库环境，非常适合使用这种索引。位图索引不适合经常更新行的在线事务（OLTP）数据库。无法创建唯一的位图索引
位图连接索引	在数据仓库环境中，对于利用连接事实表和维表的星型模式结构的查询非常有用
基于函数的索引	适用于应用了SQL函数的列。可与B树索引类型或位图索引类型结合使用
虚拟列索引	在表的虚拟列上定义的索引，适用于应用了SQL函数的列，可用来替代基于函数的索引
虚拟索引	允许通过CREATE INDEX的NOSEGMENT字句创建没有物理段或区的索引，在调优SQL时有用，因无需建立物理索引从而避免消耗资源。任何类型的索引都可以创建为虚拟的
不可见索引	该索引对查询优化器是不可见的。然而，在表中的数据被修改的同时也维护索引结构。用于在使索引对应用程序可见之前测试它。任何类型的索引都可以创建为不可见的
全局分区索引	跨分区表的所有分区或常规表的全局索引。它的类型可以是B树索引，而不能是位图索引
本地分区索引	本地索引基于分区表的单个分区。它的类型可以是B树索引或位图索引
域索引	用于具体的应用程序或程序模块
B树聚簇索引	用于聚簇表
散列聚簇索引	用于散列聚簇

B树索引

Oracle默认索引类型是B树索引。对于高基数的列值，该索引类型是非常有效的。对于大多数应用程序，该索引类型是合适的。

索引组织表（IOT）

在一个B树索引结构中存储表行的全部内容。使用索引组织表，能缩短具有精确匹配和主键范围搜索的查询时间。

CREATE TABLE 表名(列1, 列2) ORGANIZATION INDEX;

唯一索引

像唯一键约束，当数据插入到相应的表时，唯一索引将保证插入到表中的非空值都是不同的。

CREATE UNIQUE INDEX 索引名称 ON 表名(列1, 列2);

反向键索引

在需要一种方式均匀地分布索引数据，以避免将相似的值聚集在一起时，这些索引表现更好。因此，当插入大量顺序值时，如果使用反向键索引，就可以避免I/O集中在索引的某个物理磁盘位置。

CREATE INDEX 索引名称 ON 表名(列) REVERSE;

键压缩索引

有助于减少前导列经常重复的组合索引的存储和I/O要求。

CREATE INDEX 索引名称 ON 表名(列1, 列2) COMPRESS 2;

降序索引

默认情况下，Oracle用升序方式存储B树索引。例如，如果在一个列值为数值型数据的裂上创建索引，最小的数值首先出现在索引（最左边的叶节点）中，而最大的数值将被储存在最右边的叶节点上。通过对一列指定DESC可以指示Oracle反转这种顺序为降序。

CREATE INDEX 索引名称 on 表名(列 DESC);

特定的索引类型

位图索引（在Oracle企业版数据库中提供）

通常用在数据仓库环境中，适用于具有相对较低数量不同值（低基数）的列；对于在WHERE字句中使用多个AND或OR连接操作的SQL语句，位图索引也是高效的
在经常执行INSERT/UPDATE/DELETE等操作的OLTP数据库中不应该使用位图索引。这是因为位图索引的结构导致在许多单独的DML操作期间锁定多个行，从而导致高事务的OLTP系统中的锁定问题。

CREATE BITMAP INDEX 索引名称 on 表名(列);

位图连接索引（在Oracle企业版数据库中提供）

存储两个表之间的连接结果，避免检索结果时对表进行连接。在使用一个表的外键列和另一个表的主键列连接两个表时，适合使用位图连接索引
通常适用于有定期批量加载，然后不作更新的表的数据仓库环境。当更新有位图连接索引的表时，可能导致多行被锁定，因此不适用OLTP数据库。

CREATE BITMAP INDEX 索引名称 on 表1(表2.列) FROM 表1, 表2 WHERE 表1.主键 = 表2.外键;

基于函数的索引

基于函数的索引用其定义中的SQL函数或表达式创建，允许在被查询的WHERE字句中的SQL函数引用的列使用索引查找。可以为B树索引、唯一索引或位图索引。

CREATE INDEX 索引名称 ON 表(UPPER(列));

虚拟列索引（在Oracle Database 11g和更高版本中提供）

一种替代基于函数的索引的方法是在表中添加一个虚拟列，然后为虚拟列创建索引。

CREATE TABLE 表1(inv_count number, inv_status generated always as (case when inv_count <= 100 then 'LOW' when inv_count > 100 then 'HIGH' end));
CREATE INDEX 索引名称 ON 表1(inv_status);

虚拟索引

通过NOSEGMENT字句可以指示Oracle创建永远不会被使用的索引，并且不会将任何区分配给它的索引。

CREATE INDEX 索引名称 ON 表名(列) NOSEGMENT;

尽管该索引没有实例化，但还是可以通过_USE_NOSEGMENT_INDEXES初始化参数指示Oracle来确定该索引是否可能被优化器使用
在什么情况下这个索引有用？如果你想创建一个非常大的索引，并且不分配空间，以便确定优化器是否会用到它，可以用这种索引进行测试，如果有用再重新创建

不可见索引

意味着优化器为查询语句检索数据时不使用该索引。然而，当对基础表插入、更新或删除记录时，数据库仍在维护该索引结构。如果不想在影响应用程序代码的前提下。测试索引的可行性，就可以使用此功能，使用INVISBLE关键字来创建不可见索引。

CREATE INDEX 索引名称 ON 表名(列) INVISIBLE;

全局和本地分区索引（在Oracle企业版数据库中提供）

分区索引在逻辑上是一个索引，但实际上它是在几个不同的段中实现的。这能保证即使是非常大的数据库也能具有良好的性能。分区索引可以是全局的也可以是本地的。

全局分区索引是使用分区策略的索引，但这种分区并不映射到基础表的段。可以为常规表或分区表建立全局分区索引。全局分区索引实现为B树类型，并且可以定义为唯一索引。使用GLOBAL PARTITION字句创建全局分区索引。

本地分区索引必须建立在分区表上。这种索引与其基础表遵循相同的分区策略。本地分区索引的分区只包含其相应的表分区中的值。本地分区索引可以是B树索引或位图索引。使用LOCAL关键字创建此类型的索引。

域索引、B树聚簇索引和散列聚簇索引

应用程序域索引是针对一个特定的应用程序自定义的。适合自定义数据类型、文档、图像、视频和空间数据。

B树聚簇索引是聚簇表上定义的索引。B树聚簇索引将一个聚簇键与一个数据库块地址相关联。该索引类型与聚簇表一同使用。

散列聚簇索引也用于聚簇表，与B树聚簇索引的差异是，前者使用散列函数取代索引键。

确定需要建立索引的列

1. 为每个表定义主键约束：这导致在主键指定的列上自动创建索引
2. 在要求唯一且不同于主键列的列上创建唯一键约束：每个唯一键约束导致在约束中指定的列上自动创建一个索引
3. 手动创建外键列上的索引：这是为了得到更好的性能，以避免某些特定的锁问题

主键列和唯一键列的索引

对于主键约束或在表上定义的唯一键约束，如果没有已定义的索引，Oracle将自动创建一个合适的B树索引

外键列的索引

Oracle不会自动创建外键列的索引。我们建议在外键列上创建B树索引的原因之一是，外键列经常在WHERE字句中被引用，并因此可以改善这些查询的性能。

当外键列上存在索引时，可以避免减少锁定的问题。也就是说，当插入或删除子表中的记录时，父表上放置一个表级别的锁，该锁将阻止其他进程在父表中插入或删除记录。在OLTP数据库中，当有多个进程同时插入和删除子表的记录时，这可能会造成问题。

其他适合创建索引的列

原则：根据查询表时使用的列制定索引策略。

1. 为经常用作WHERE子句中谓词的列创建索引，如果WHERE子句中使用表的多个列，可以考虑使用组合（复合）索引
2. 为在SELECT子句中使用的列创建覆盖索引。覆盖索引包括查询返回的所有列，在这种情况下，Oracle可以使用索引本身结构（而不是表）来满足查询的结果
3. 考虑为在ORDER BY、GROUP BY、UNION或者DISTINCT子句中的列创建索引

索引指南

只有确定了SELECT语句在性能上的改进与索引所消耗的空间成本和更新表时的开销相比是否值得，才能确定是否应该使用索引

索引指南

指南	论证
创建所需数量的索引，但尽量少创建索引，明智地添加索引。首先要测试以确定可量化的性能收益	索引提高性能，但也消耗磁盘空间，不要添加不必要的索引
对表执行的查询所需的性能，应该能成为制定索引策略的基础	为在SQL查询中使用的列创建索引将最大限度地提升性能
考虑使用SQL调优顾问或SQL访问顾问获得索引的建议	这些工具提供了建议和第二双进行索引决策的眼睛
为所有表创建主键约束	这将自动创建一个B树索引（如果在主键列上还没有建立索引）
在合适的地方创建唯一键约束	这将自动创建一个B树索引（如果在唯一键列上还没有建立索引）
为包含外键的列创建索引	连接表时，外键列通常包含在WHERE子句中，从而能提高SQL SELECT语句的性能，在外键列上创建一个B树索引，还可以减少在更新和插入子表时的锁定问题
小心选择和测试小表的索引（小表少于几千行）	即使对于小表，有时候访问索引也可能比全表扫描性能更好
使用正确的索引类型	正确使用索引能最大限度地提高性能
如果不能证明使用不同类型索引获得性能增益，那就使用基本的B树索引	对于大多数具有高基数列值的应用程序，都适合使用B树索引
数仓环境中，考虑使用位图索引	对不经常更新的低基数列来说，使用这些索引是理想选择。位图索引适用于星型模式事实表的外键列，前提是经常会对事实表运行使用AND和OR连接条件的查询
考虑为索引使用单独的表空间（与表分离）	表和索引可能有不同的存储、备份和恢复要求。单独的表空间，可以把索引和表分开管理
让索引从表空间继承它的存储属性	这使得维护和故障排除更容易
不要重建索引，除非有充分的理由这样做	重建索引通常是不必要的，除非索引损坏或需要把索引移动到不同的表空间
监测索引，并删除不被使用的索引	这样能释放物理空间，并提高DML语句的性能
删除索引之前，考虑把它标记为不可用或不可见的	这使你在删除索引之前，可以更好的确定是否有任何性能问题。这些选项使得可以重建或重新启用索引而无需用DDL来创建语句

 

索引的存在主要是为了提高查询性能，因此仔细考虑如何实现索引是至关重要的。精心设计的索引策略将会使数据库应用程序的性能优异。相反，欠考虑的计划将导致性能不佳。

索引占用磁盘空间，并与表分开存放。然而，索引定义在表的一个或多个列上，从这个意义上说，索引不能脱离表而单独存在。

Oracle提供了广泛的索引类型和功能。在大多数情况下，使用默认的B树索引就可以了。使用其他类型的索引之前，请确保你了解性能收益。你应该知道Oracle提供的索引有哪些功能，并知道在什么样的情况下，应该使用哪种专门的索引。

我们建议在主键列、唯一键列和外键列上创建索引。这是个很好的起点。然后对于执行缓慢的SQL语句进行分析，观察会用到哪些列。这会为你提供额外的候选索引列，这些索引建议奠定了最大限度地提高了SQL查询性能的基础。

B树索引

https://blog.csdn.net/xiaoxu0123/article/details/5451810

B树索引是Oracle默认的索引类型。因为表中的行标识符（ROWID）和相关的列值存储在一个平衡的树状结构的索引块中，所以该索引类型被称为B树索引。使用Oracle的B树索引有以下几个原因：

1. 提高SQL语句的性能
2. 强制执行主键和唯一键约束的唯一性
3. 减少通过主键和外键约束关联的父表和子表间潜在的锁定问题

如果一个表的列值（或列组合）在所有行中都是相当独特的，那么创建B树索引通常会提高查询性能。如果索引结构本身包含满足查询结果所需的表列值，那么还能实现额外的性能提升。在这种情况下，不必访问表中的数据块。了解这些概念将有助于确定哪些列需要索引，并且确定组合索引是否对某些特定查询更有效，而对其他查询却不是最佳的选择。

当向表插入行时，Oracle将分配由物理数据块组成的区。Oracle还将为索引分配块。对于每个插入到表中的记录，Oracle还将创建一个包含ROWID和列值得索引条目。每个索引项的ROWID指向存储该表的列值的数据文件和块。当从一个表及其对应的索引选择数据时，有三种基本情况：

1. SQL查询所需的所有表的数据都在索引结构中。因此，只需要访问索引块，不需要从表中读块
2. 查询所需的所有信息没有都包含在索引块中。因此，查询优化器选择既访问索引块也访问表块来检索需要的数据，以满足查询的结果
3. 查询优化器选择不访问索引。因此，只访问表块

场景一：所有数据位于索引块

介绍两种情况。在每种情况下，执行查询需要的所有数据，包括返回给用户的数据，以及在WHERE子句中被评估的数据，都位于该索引中。

索引范围扫描（index range scan）：如果优化器确定它使用索引结构检索查询所需的多行时是有效的，那么就使用这种扫描。索引范围扫描被广泛用于各种各样的情况。
索引快速全扫描（index fast full scan）：如果优化器确定表中的大部分需要进行检索，那么就使用这种扫描。但所有需要的信息都存储在索引中。由于索引结构通常比表结构小，优化器确定全索引扫描（比全表扫描）更高效。这种情况对统计（count）值的查询是很常见的。