1 索引（index）

• 索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；
• Oracle存储索引的数据结构是B*树（平衡树），位图索引也是如此，只不过是叶子节点不同B*数索引
• 索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。

索引特点：

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响

5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

6）oracle创建主键时会自动在该列上创建索引

1.1 索引类型

1.1.1 逻辑分类

1.1.1.1 唯一索引

索引字段值是唯一的，比如主键。

Create unique index index_name on table_name(column)

1.1.1.2 非唯一索引

1.1.1.3 单列索引

索引作用于表的一个字段。

1.1.1.4 复合索引

对于表的多个字段一起建立索引。建立符合索引时需要把常用的字段放在第一位，在oracle9i以前在使用时也需要有第一位的列，否则数据库优化引擎不会走索引，9i以后引入了跳跃索引skip index,where字句中没有使用第一列也有可能走索引。

比如创建如下索引

Create index idx_emp on emp(sex,name).

使用时没有加入sex，oracle也可能会走skip index。

select * from emp where name=’xxx’,

在oracle内部，其实是生成了两条sql语句,然后对两个结果的rowid进行合并。

Select * from emp where sex=’M’ and name=’xxx’

Union

Select * from emp where sex=’F’ and name=’xxx’

从上面可以知道如果sex字段有很多值，oracle内部会生成很多条sql语句，因此性能大大降低，是否使用skip index取决于第一列的选择性。跳跃式索引相对于直接索引要慢，但相对于全表扫描会快很多。

Oracle最多可以使用32个字段的复合

1.1.1.5 函数索引

比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。

Create index index_name on table(函数(列))

1.1.2 物理分类

1.1.2.1 分区索引

分区索引是在每个分区上单独创建索引，它能自动维护，drop或者truncate某个分区时，不影响其他分区索引。B树索引和位图索引都可以分区，hash索引不可以分区。分区索引可以提到性能和可维护性。使用分区表和索引之后oracle支持并行DML操作

集中分区方式：

• 表未分区，索引分区
• 表分区、索引未分区
• 表分区、索引都分区

本地分区索引包含它所关联的分区的键和rowid，表分区和索引分区是相同的。本地分区索引可以独立重建而不影响其他分区。

全局索引就是在全表上创建索引，他可以有自己的分区，可以和分区表不一样。全局索引只能是B树索引，oracle在默认下不会维护全局分区索引。如果一个分区被删除、分割、增加就必须重建全局分区索引。除非在修改时指定alter table * update global indexs.

在频繁删除表的分区或者数据更新频繁时为了维护方便避免使用全局索引。Oracle会对主键自动创建全局索引

1.1.2.2 非分区索引

1.1.2.3 B树索引

B-Tree、B+Tree B*Tree

B树索引是我们在oracle数据库中最常用的索引，在详细介绍访问方法之前，我们看一下B-TREE索引的结构（图片来源网络）

oracle的B树索引就好像一颗长到的树，他包含两种类型，一种是索引分支块（根节点块，分支节点块）一种是索引叶子块（叶子节点块）。分节点用来搜索，叶子节点用来存储数据。根节点存储索引的低层分支节点的数据。 由于所有的叶子节点均会自动的存储成相同的深度，所以称为“平衡树索引”， 故此，从任何叶子处检索数据消耗的时间都是相同的。

对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。 比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。

对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。

当用户创建索引时，Oracle 取得所有被索引列的数据并进行排序，之后将排序后索引值和与此值相对应的 rowid 按照从下到上的顺序加载到索引中

接下来介绍一个索引查询的流程，从上往下，第一层为根节点，第二层为分支节点，第三层为叶子节点（包含了列值和rowid）。比如我们的条件为where=29，(补充说明如果被索引的列存储的是字符数据，那么索引值为这些字符数据在当前数据库字符集中的二进制值)就从跟节点开始查询，29在0-500中，指向分支节点最左边第一个分支节点块（也就是B1），就B1中去找，发现29在0-200中，指向叶子节点的L1，于是在L1中找到29的值和响应的rowid。如果只查找索引列的值，就不用根据rowid去表中查找了，如果还要查找值29这行的其他列的值就得根据rowid去表里查查询（这个过程叫做回表查询）。叶子节点还有个双向链表（如图）。在通过索引进行范围扫描时会起作用，比如要查找值29-700，如果当查找到值29的时候，不就会再从跟节点开始查找其他的值，而是根据本叶子节点链表的指向去查找其他的值。

删除一行只导致对索引条目进行逻辑删除。已删除行所占用的空间不可供后面新的叶条目使用。

1.1.2.4 位图索引

位图（bitmap）索引是另外一种索引类型，它的组织形式与B树索引相同，也是一棵平衡树。与B树索引的区别在于叶子节点里存放索引条目的方式不同。从前面我们知道，B树索引的叶子节点里，对于表里的每个数据行，如果被索引列的值不为空的，则会为该记录行在叶子节点里维护一个对应的索引条目。

而位图索引则不是这样，其叶子节点里存放的索引条目如下图所示。

假设某个表T里所有的记录在列C1上只具有三个值：01、02和03。在表T的C1列上创建位图索引以后，则叶子节点的内容如图所示。可以看到，位图索引只有三个索引条目，也就是每个C1列的值对应一个索引条目。位图索引条目上还包含表里第一条记录所对应的ROWID以及最后一条记录所对应的ROWID。索引条目的最后一部分则是由多个bit位所组成的bitmap，每个bit位就对应一条记录。

当发出where c1=’01’这样的SQL语句时，oracle会去搜索01所在的索引条目，然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为1，则说明第一条记录上的C1值为01，于是返回第一条记录所在的ROWID（根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID）。第二个bit位为0，则说明第二条记录上的C1值不为01，依此类推。另外，如果索引列为空，也会在位图索引里记录，也就是将对应的bit位设置为0即可。

如果索引列上不同值的个数（索引列基数）比较少的时候，比如对于性别列（男或女）等，则使用位图索引会比较好，因为它对空间的占用非常少（因为都是用bit位来表示表里的数据行），从而在扫描索引的时候，扫描的索引块的个数也比较少。可以试想一下，如果在列的不同值非常多的列上，比如主键列上，创建位图索引，则产生的索引条目就等于表里记录的条数，同时每个索引条目里的bitmap里，只有一个1，其它都是0。这样还不如B树索引的效率高。

如果被索引的列经常被更新的话，则不适合使用位图索引。因为当更新位图所在的列时，由于要在不同的索引条目之间修改bit位，比如将第一条记录从01变为02，则必须将01所在的索引条目的第一个bit位改为0，再将02所在的索引条目的第一个bit位改为1。因此，在更新索引条目的过程中，会锁定位图索引里多个索引条目。也就是同时只能有一个用户能够更新表T，从而降低了并发性。

位图索引比较适合用在数据仓库系统里，不适合用在OLTP系统里。

在B树索引中可以使用行锁，在位图索引中，因为对rowid进行了压缩（一个rowid范围+位图），因此每个锁定都是一个rowid范围，因此对表的位图索引列更新操作时并发性很差。

位图索引使用限制：

• 基于规则的优化器（RBO）不会考虑位图索引
• 执行alter table修改位图索引列时，位图索引会失效
• 位图索引不包含任何列数据，不能用于任何类型的完整性校验，比如唯一性、主键
• 位图索引不能申明唯一索引
• 不要在繁忙的oltp中使用位图索引。

1.1.2.5 反转索引

当载入一些有序的数据时，索引会碰到与IO相关的一些瓶颈，在载入期间，某部分索引和磁盘肯定会比其他部分使用得频繁得多，为了解决这个问题可以使用反转索引，这样oracle会把数据分散到不同数据块。

1234，1235，1236的字段，如果使用反转索引就变成了4321，5321，6321，结果索引会更新到不同的索引块中。

创建反转索引的语法：

Creat index index_name on table(column)

Reverse

Pctfree xxx

Storage()

Tablespace xx

也可以修改现有的索引为反转索引

Alter index index_name rebuild reverse.

1.1.3 形式分类

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致

1.1.3.1 聚集索引

聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

a.此列包含有限数目的不同值；

　b.查询的结果返回一个区间的值；

　c.查询的结果返回某值相同的大量结果集

create clustered index index_name;

如果某张表的大多数访问都是按照某个索引做索引扫描，可以考虑把这个索引设置为聚集索引提高访问效率。

1.1.3.2 非聚集索引

非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针rowid的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。建议使用非聚集索引的场合为：

a.此列包含了大量数目不同的值；

b.查询的结束返回的是少量的结果集；

c.order by 子句中使用了该列。

1.2 Oracle扫描数据方式

1.2.1 全表扫描full table scan

Fts会读取表中每一行，多块读会大大减少IO的次数，利用多块读可以大大提高全表扫描的速度，只有在全表扫描时才能使用多块读。

1.2.2 通过rowid进行行读取table access by rowid

Rowid指定了数据的文件、块号、行号，是数据库中访问单行数据最快的方式，采用单块读的方式。

1.2.3 索引扫描或者索引查找Index scan index lookup

通过索引找到数据行的rowid，根据rowid直接查找表里面的数据，采用的是单块读。因为索引小、经常读取，通常通过索引查找到数据行的rowid往往是在cache中执行，是逻辑读，因为数据较大，通过rowid查找数据行往往是物理读，性能较低。

根据索引的类型和where条件的不同，有四种不同的索引扫描

1.2.3.1 index unique scan索引唯一扫描

通过唯一键、主键，oracle通常返回一行数据，因此采用的是唯一索引扫描。

1.2.3.2 index range scan索引范围扫描

• 对唯一索引使用>、<、<>、>=、<=、between
• 在复合索引上使用部分列进行查询，导致查询出多行数据
• 对非唯一索引列进程查询

1.2.3.3 index full scan索引全扫描

查询出的数据必须全部从索引中得到，不用再访问数据行

1.2.3.4 index fast full scan 索引快速扫描

扫描索引块中所有数据块，这点与index full scan相似，但索引快速扫描不进行数据的排序，在这种方式下，可以使用多块读功能，也可以使用并行读功能，最大化数据的吞吐量，通常需要较少的IO。

1.3 限制索引

Oracle中会存在某些条件下限制索引的情况。Oracle中存在优化器，会根据数据分布的相关统计信息决定是否使用索引，因此不是说where中添加了索引列条件就一定会走索引。

1.3.1 索引列使用<>或者!=

1.3.2 索引列使用is null 或者is not null

如果索引列在某些行是null，索引列中就不会有相应条目，位图索引除外。位图索引中的bitmap会记录每行数据的列索引信息，如果为null，相应的bit位是0。

1.3.3 索引列使用函数

如果不是函数索引，则对索引列使用函数不会走索引。根据实际情况可以考虑在索引列的查询值上使用索引，比如date是索引列

trunc(date)=’’ –不走索引,

date=to_date(‘’,’yyyyMMdd’)—走索引

1.3.4 比较不匹配的数据类型

这个不容易被发现，oracle会把列数据的类型转换为比较值的类型，比如sno是varchar2类型，select * from student where sno=23432，oracle在sno自动加上to_number。为什么不加载索引列的值23432上面呢，因为它是一个常量，常量是不能改变。

1.4 索引相关操作

1.4.1 索引失效rebuild

alter index UK_T_FAULT_OBJ_ARCH rebuild

analyze table T_FAULT_OBJ_ARCH compute statistics for table for all indexes

1.4.2 索引重命名

Alter index index_name rename new_index_name;

1.4.3 索引合并

alter index index_sno coalesce;

表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低

1.4.4 删除索引

Drop index index_name;

1.4.5 查村执行计划

1、先执行执行计划分析Explain plan for select * from dual;

2、查询执行计划结果select * from table(dbms_xplan.display)

1.4.6 查询索引空间大小

Select Segment_Name, Sum(bytes) / 1024 / 1024/1024 as Gsize

From User_Extents

Group By Segment_Name

order by Gsize desc