oracle 索引基本原理

一.索引基本概念

oracle提供了两种方式:从表中读取所有行(即全表扫描)，或者通过ROWID一次读取一行。

如果只访问大数据量表中的5%的行，并且使用索引标识需要读取的数据块，这样话费的I/O较少.

索引对性能改进的程度：1.取决于数据的选择性 2.数据在表的数据块中的分布方式

当数据分散在表的多个数据块中时，最好是不使用索引，而是选择全表扫描。执行全表扫描时，oracle使用多块读取以快速扫描表，基于索引的读是单块读。因此在使用索引时的目标是减少完成查询所需的单块读的数量。

增强索引会降低insert语句的性能(因为需要同时对表和索引进行更新).大量行的delete操作将会由于表中存在索引而变慢

在表上加一个索引都会使该表上insert操作的执行时间变成原来的三倍，再加一个索引就会再慢一倍。

视图：

DBA_INDEXS

USER_INDEXS

ALL_INDEXS

显示表的索引

USER_IND_COLUMNS

DBA_IND_COLUMNS

ALL_IND_COLUMNS

显示所有被索引的列

二.不可视索引

每插入一条记录时，就会更新所有索引.oracle允许关闭索引(使其不可见)，但是索引上的维护工作还会继续。

alter index idx1 invisible

alter index idx2 visible

create index .. invisible

create index dept_inv_idx on dept_rich(deptno) invisible;

select count(*) from dept_rich where deptno=30;(索引不可见)

在执行计划里是看不到使用索引了

可以使用强制索引使用，通过USE_INVISIBLE_INDEXS提示，或者把初始化参数OPTIMIZER_USE_INVISIBLE_INDEXS设置成true。

select /*+ USE_INVISIBLE_INDEXS */ count(*) from dept_rich where deptno = 30;(通过提示强制使用)

执行计划可以看到使用索引了

在不将其变成不可见的前提下，也可以使用NO_INDEX提示来关闭一个索引.

select /*+ no_index(dept_rich dept_rich_inv_idx) */ count(*) from dept_rich where deptno = 30;(强制不使用带提示的索引)

会发现执行计划走的全表扫描

可以随时将这个索引设成不可见

alter index dept_rich_inv_idx invisible;

三.组合索引

当某个索引包含多个列时，我们称这个索引为"组合索引" 或 "复合索引".

引入的索引跳跃式扫描增加了优化器在使用组合索引时的选择，所以在选择索引中的列顺序时应该谨慎。

索引的第一列应该是最有可能在where子句中使用的列，并且也是索引中最具选择性的列。

create index emp_id1 on emp(empno，ename，deptno);

跳跃式扫描select job,empno from emp where ename='RICH'; 即使where子句中没有指定empno值，优化器也可能会选择使用该索引.也可能使用索引快速扫描或全表扫描。

如果在where子句中使用索引的第三列，也会产生相同的情况。

优化器可能选择索引跳跃式扫描，索引快速扫描或全表扫描。

最常见的索引扫描方式是唯一扫描和范围扫描。在唯一扫描中，数据库知道索引包含每一个值都是唯一的。在范围扫描中，数据库将根据查询条件从索引中返回多个符合条件的值。上面的都是范围扫描

使用create unique index命令可以创建唯一索引.

在创建主键约束或唯一性约束时，oracle将基于指定的列自动创建唯一索引(除非使用disable子句创建约束)。如果创建多列的主键，oracle将创建组合索引，其中的列的排列顺序和创建主键时指定的列的顺序一致。

四.索引抑制

在SQL中有很多陷阱会使一些索引无法使用

1.使用不等于运算符(<> , !=)

索引只能查找表中已存在的数据，每当在where子句中使用不等于运算符时，其中被用到的列上的索引都将无法使用。

例如：select cust_id，cust_name from customers where cust_rating <> 'aa';

oracle在分析表的同时收集表中数据分布的相关统计信息。通过这种方法，基于成本的优化器就可以决定在where子句中对一些值使用索引，而对其他的值不使用.

可以通过create index 命令的compute statistic子句，在创建索引的同时分析它们。

2.使用IS NULL或IS NOT NULL

当在where子句中使用IS NULL或IS NOT NULL的时候，因为NULL值并没有被定义(oracle不会在B树索引中对NULL值索引).所以索引的使用会被抑制。数据库中没有值等于NULL;甚至NULL也不等于NULL.

如果被索引的列在某些行中存在NULL值，在索引中就不会有相应的条目(除非使用位图索引，这是位图索引对于NULL搜索通常很快的原因)。

例如:select empno，deptno from emp where sal is null;

即使sal列上有索引，也会进行全表扫描

只有在表中每一行值都非NULL或是使用alter table命令的default子句时，才可以为列设置NOT NULL属性.

alter table emp modify (sal not null);

create table p(id int，nameid number(4) default 10);

insert into p values(1，NULL);

insert into p values(2,20);

显示:

ID NAMEID

1

2 20

创建表时对列指定NOT NULL或default 可以帮助避免出现的性能问题.

3.使用LIKE

条件中有LIKE关键字会使用索引。

通常有两种写法 LIKE '%some%'  或LIKE 'some%'

当%在前面的时候，索引不会被使用，但是当值在%前面时候，oracle可以使用索引

4.使用函数

除非使用基于函数的索引，否则在SQL语句的where子句中对存在的索引的列使用函数时，优化器会忽略索引.一般常见的索引如：TRUNC,SUBSTR,TO_DATE,TO_CHAR和INSTR等。

例如下面的就会使用全表扫描

select empno，ename，deptno from emp where trunc(hiredate)='01-MAY-01';

改成下面的就可以了

select empno，ename，deptno from emp where hiredate > '01-MAY-01' and hiredate < (TO_DATE('01-MAY-01') + 0.99999);

通过改变所比较的列上的值，而不用改变列本身，就可以启用索引，这样可避免全表扫描.

5.比较不匹配的数据类型

一种很难解决的性能问题是比较不匹配的数据类型。oracle不但不会对那些不兼容的数据类型报错，反而会做隐式数据转换。例如：oracle可以隐式得转换varchar2类型的列中的数据去匹配数值类型数据。

如果account_number列是varchar2数据类型，下面将进行全表扫描

select bank_name，address，city，state，zip from banks where account_number=99999;

oracle会自动转换成 to_number(account_number) = 99999; 这样就抑制了索引的使用.

如果上面的语句加上单引号就会使用索引了

select bank_name，address，city，state，zip from banks where account_number='99999';

五.选择性

oracle基于查询和数据，提供了多种方法来判断使用索引的价值。首先判断索引中的唯一键或不同键的数量。可以通过对表或索引进行分析的方法来确定不同键的数量，之后就可以查询USER_INDEXES视图的DISTINCT_KEYS列来查看分析结果。比较一下不同键的数量和表中的行数(USER_INDEXES视图中的NUM_ROWS)，就可以知道索引的选择性.索引的选择性越高意味着一个索引值返回的行数就越少，该索引就越好。

索引的选择性可以帮助基于成本的优化器来决定执行路径.

六.集群因子

集群因子是索引与它所在的表相比较的有序性度量，它用于检查在索引访问之后执行的表查找的成本。

如果具有较大的集群因子，就必须访问更多的表数据块才可以获得每个索引块中对应的数据行。

如果集群因子接近于表中的数据块数量，就表示索引对应数据行的排序情况良好；但是集群因子接近于表中的数据行数量，就表示索引对应的数据块排序情况不佳。

集群因子计算简要:

1.按顺序扫描索引

2.将当前索引值指向的ROWID的数据块部分与前一个索引值指向的数据块进行比较(比较索引中的邻近行)

3.如果ROWID指向该表中不同的数据块，就增加集群因子(对整个索引执行该操作)。

CLUSTERING_FACTOR列是USER_INDEXES视图中的一列，该列反映了数据相对于已索引的列是显得有序。

七.二元高度 (Binary Height)

索引的二元高度对把ROWID返回给用户进程时所要求的IO数量起到关键作用。二元高度的每个级别都会增加一个额外的块读取操作，而且由于这些块不能按顺序读取，它们都要求一个独立的IO操作.

例如:下面的一个二元高度为3的索引，需要读4个快才能返回一行数据给用户，其中3次用来读索引.1次用来读表。随着索引的二元高度的增加，检索数据所要求的IO次数也会随之增加.

________

boxter  idx block id  |  |

king idx block id --->king idx block id | |

 holman idx block id------>bolman  row id |  |

   histan  row id ------>_______|    

level 1 level 2 level 3   表

----------------------二元高度为3的索引(level为3叶节点所在位置)---------------------

通过查询DBA_INDEXES视图的BLEVEL列来查看它的二元高度:

EXECUTE DBMS_STATS.GATHER_INDEX_STATS('SCOTT','EMP_ID1');

select blevel，index_name from dba_indexes where index_name='EMP_ID1';

表中索引列的非NULL值数量的增加和索引列中值的范围狭窄是二元高度增加的主要原因:索引上如果有大量被删除的行，它的二元高度也会增加。重建索引可能使二元高度降低。虽然这些步骤减少了针对索引执行的IO数量，但对性能的改进却可能很小。如果一个索引中被删除的行接近20%~25%，重建索引会降低二元高度以及在一次IO中所读取的索引块中的空闲空间.

数据库中的数据块越大，索引的二元高度就越低。二元高度每增加一个级别，在DML操作期间就会额外增加性能成本。

关于BLEVEL和索引高度的更多细节

B树级别是指一个索引从它的根块到其叶块的深度。0层表名根块和叶块在同一个级别。所有索引都是从一个叶块开始，这时它代表一颗0级的B树。当行被逐渐添加到索引中时，oracle会把数据放到叶块中，随着不断有数据插入，一旦初始叶块填满，两个新块就会被创建出来，oracle以两种方式处理这个操作，90-10或50-50索引分裂法。被插入的值决定了使用哪种分裂方式:

1.如果新值大于该索引中已有任何值，那么oracle将使用90-10分裂法，把当前块中的值复制到一个新块，将新值放到另外一个块中。

2.如果新值不是该索引中最大的值，那么oracle会使用50-50分裂法，将较小的一半索引值放到一个新块中，将较大的另一半索引值放到另外一个新块中。

只有在根节点分裂的情况下，索引会创建两个新块，当前的根块的内容被分割到两个新的分支块中，形成一个更高的索引树的顶部。

1.更新操作对索引的影响

索引只有在表中组成索引的列被更新时才会受到影响。当更新组成索引的表列时，索引上会执行一个删除和插入的操作。旧值被标记为已删除，与原索引条目对应的一个新值被插入。因此，索引上没有真正意义上的"更新"。索引条目通过oracle的延迟块清理功能得以清理。只有在索引条目被删除而且块被清理后，索引块中的空间才能被新条目重用。

2.删除操作对索引的影响

索引上的删除操作，并没有真正从索引中删除条目以获得空间。实际上，当表中一条记录被删除时，相应的索引条目被标记为已删除，在清理过程清理之前，仍然保留在索引中。

3.更新和删除操作对索引的影响

删除操作仍然把数据块留在叶块中，需要由清理过程清理。

在同一事务中对索引的删除/插入操作，往往会明显增大索引的大小，这种情况一般发生在同一事务执行大量这样的操作。删除操作自身不会引起索引高度或BLEVEL的增加，只不过反应了如何重用被删除行所占空间的更大的问题。所以最好拆分原事务，这有助于重用空间，不会导致索引人为地增长到大于它应有的大小。

索引块分裂会产生大量的重做日志。

使用本地管理的表空间以避免碎片和极少重建索引。

4.数据块大小对索引的影响

从分支块到根块都可能分裂，这种行为会导致索引的高度和分支的增加。分支索引块分裂的次数，可以通过使用较大的索引块尽量减少，这是一些专家认为应使用更大的块创建索引表空间的原因。如果每个索引块能容纳更多的数据索引分裂的出现的频率将低很多，因此可以减少索引的分支和树叶块数。将索引移到具有更大的块大小的表空间时需要重建，这时会删除所有被标记为删除的条目，压缩索引使用空间，包括回收被标记为已删除的条目的空间和条目已经删除但还没有回收或重用的空间。

八.使用直方图

在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优化器就可以针对查询条件决定如何使用索引。如果条件返回少量行，就采用索引;如果条件返回许多行，就不采用索引。直方图的使用不限于索引。表的任何列上都可以构建直方图.

构造直方图最主要的原因就是帮助优化器在表中数据出现严重偏斜时做出更好的规划。如果一到两个值构成了表中的大部分数据，使用相关的索引就可能无法协助减少满足查询所需的IO数量，创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或者何时根据where子句中的条件值，表中80%的记录会返回。

首先要指出它的大小，该大小与直方图所需的桶数相关。每个桶包含列值和行数的相关信息。

execute dbms_stats.gather_table_stats('scott'，'company'，METHOD_OPT => 'FOR COLUMN SIZE 10 company_code');

oracle 的直方图分为高度均衡和频率两种。高度均衡直方图里的所有桶都有相同的行数。桶的起点和终点取决于包含这些值的行数。频率的直方图规定每个存储桶的值的范围，然后统计出这个范围内的行数，这并不是一个理想的选择。

如果使用频率均衡方式的桶，多数桶都只有3行记录;有一个桶却有73行记录。如果使用高度均衡方式的桶，每个桶都有相同数目的行，多数桶的终点都是1430，这也反映了数据的偏斜分布。

如果表中的数据分布的较不均匀，直方图会为基于成本的优化器提供数据分布的均衡图(把数据平均分布到各个桶)。在没有数据偏斜的列上使用直方图并不会提高性能.

默认情况下，oracle会为直方图产生75个桶，可以把SIZE的值指定在1到254之间1^4。

七.快速全扫描

在索引的快速全扫描过程中，oracle读取B树索引上的所有树叶块。这个索引可以顺序方式读取，这样一次可以读取多个块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。相比全表扫描，快速全扫描通常需要较少的物理IO，并且查询可以更快的完成。

如果表查询中的所有列都被包括在索引里，而且索引的前导列并不在where条件中，就可以使用快速全扫描(可能需要制定第7章讲到的INDEX_FFS提示)。

例子:在emp表上empno，ename和deptno上有一个组合索引。

select empno，ename，deptno from emp where deptno = 30;

由于sql语句中的所有列都包括在索引中，因此可以使用快速全扫描。在查询中有连接的情况下，当查询仅仅涉及被索引的连接键列上的数据时，索引快速全扫描往往会被派上用场。另外一种可选方案是，oracle可能执行索引的跳跃式扫描。

如果索引相对于表的总体尺寸来说很小，快速全扫描就可以使应用程序的性能徒增，如果表中有一个包含了大部分列的组合索引，索引可能要比真实的表大，这样快速全扫描反而会降低性能。

八.跳跃式扫描

索引跳跃式扫描特性允许优化器使用组合索引，即便索引的前导列没有出现在where子句中。索引跳跃式扫描比索引全扫描快得多，这是因为它只需要执行很少量的读操作。

create index skip1 on emp5(job，empno);  在11g中索引的统计信息在创建的时候已经收集了

select count(*) from emp5 where empno = 7900;

逻辑读 6826

物理读 6819

select /*+ index(emp5 skip1) */ count(*) from emp5 where empno = 7900;

逻辑读21

物理读17

该跳跃式扫描逻辑读21个 其中物理读17个

为了让优化器选择跳跃式扫描，可能需要在查询中像该程序清单所示的那样使用提示，提示影响了优化器，使其偏向您所指定的执行路径。

对于那些有 组合索引的大型表而言，索引跳跃式扫描特性可以再前导列不在限制条件中时提供一种快速访问方法。

九.索引的类型

1.B树索引

B树索引在oracle中时通用索引，是创建索引时的默认索引类型。B树索引可以是单列索引，也可以是组合/复合索引。B树索引最多可以包括32列。

oracle必须通过两个分支才能到达包含有ROWID的树叶块。在每个分支块中，树枝行包含链中下一个块的ID号。

树叶块包含了索引值，ROWID，以及指向前一个和后一个树叶块的指针。oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上非空的每个数据行的ROWID值，oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中一列上包含NULL值，这一行会在索引中，有NULL值的列被处理为空.

索引列的值都存储在索引中。因此，可以建立组合符合索引，在不访问表的情况下直接满足查询需要的结果，这就不用从表中检索数据，从而减少了IO量。

2.位图索引

位图索引时决策支持系统和数据仓库的理想选择。它们不应该用于事务处理应用程序中的表。在数据量非常大的基数不高的列上建立位图索引，可以实现对这类表的快速访问。尽管位图最多可以包括30列。

例如:表上可能包含一个sex的列，它的值可能值:男和女.它的基数是2，如果频繁访问sex列的数据，位图索引时首选。当一个表包含了多个位图索引，oracle就可以合并从每个位图索引得到的结果集，快速排出不必要的数据。

create bitmap index dept_idx on dept(deptno);

由于位图索引的尺寸相对于B树索引来说小很多，因此用来访问低基数列的速度就很快。

不要在高负载的OLTP应用程序中使用位图索引。B树索引中，一个ROWID对应一个索引值中，如果更新表和上面的索引时，oracle可以锁定单独的行，位图索引的索引值使用压缩格式存储，其中一个索引值会包含一系列的ROWID.因此oracle不得不在更新一个给定值时锁定与其对应的所有ROWID。

位图索引有以下限制：

基于规则的优化器不会考虑位图索引

当执行alter table 语句并修改包含有位图索引的列时，会使位图索引失效。

位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。

位图索引不能被声明为唯一索引

位图所以最多包含30列。

3.哈希索引

使用哈希索引必须使用哈希集群。建立集群或哈希集群的同时，也就定义了集群键。这个键告诉oracle如何在集群上存储表。哈希索引可能是访问数据库中数据的最快方法，但它也有自身的缺点，在创建哈希集群之前必须知道集群键上不同值的数目，而且在创建哈希集群的时候指定这个值。低估了集群键的不同值的数目可能会造成集群的冲突(两个集群的键值拥有相同的哈希值)。这种冲突时非常消耗资源的，会造成使用更多的内存来存储额外行，造成额外的IO.

哈希索引在限制条件是确定的值而不是范围的情况下非常有用。

4.索引组织表

索引组织表会把表的存储结构改成B树结构，以表的主键进行排序.这种特殊的表和其他类型的表一样，可以在表上执行所有的DML和DDL语句。ROWID并没有被关联到表的行上。

对于一些涉及精确匹配和范围搜索的句子，索引组织表提供了一种基于键的快速数据访问机制。

如果不会频繁地根据主键列查询数据，那么需要在索引组织表中的其他上创建额外的索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。

可以在索引组织表上建立额外的索引.

5.反键索引

当载入一些有序数据时，索引可能会碰到一些与IO相关的瓶颈。可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系机构上。

数据1234就被存储成4321.结果就是索引会为每次新插入的行更新不同的索引块。

如果您的磁盘数量有限，同时还要执行大量的有序载入，就可以使用反键索引。不可以将反键索引与 位图索引或 索引组织表结合使用。

6.基于函数的索引

create index emp on emp(upper(job));

尽管基于函数的索引非常有用，但在建立之前必须考虑下面的问题:

1.能限制在这个列上使用的函数吗？如果能，能限制所有的这个列上执行的函数吗？

2.是否有足够的应付额外索引的存储空间？

3.在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？

为了使优化器使用基于函数的索引，必须把初始参数QUERY_REWRITE_ENABLED设定为true

7.分区索引

分区索引就是简单地把一个索引分成多个片段。通过把索引分成多个物理片段，可以访问更小的片段，并且可以把这些片段分别存放在不同的磁盘驱动器。B树索引和位图索引都可以被分区，而哈希索引不可以被分区。

有两种类型的分区索引：本地索引和全局索引。每个类型都有两个子类型，有前缀索引和无前缀索引。如果使用了位图索引，就必须是本地索引。

对索引分区主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

1.本地索引

本地索引是与表使用相同的分区键和范围界限的索引。本地索引的每个分区只包含了它所对应的表分区中的键值和ROWID。本地索引可以是B树索引或位图索引。

有前缀的索引：包含了分区键并把它们作为索引的前导列的索引。本地的有前缀索引可以让oracle快速剪裁一些不必要的分区。也就是说，没有包含在where条件子句中的任何值的分区将不会被访问，这样也提高了语句的性能。

无前缀的索引：没有把分区键的前导列作为索引的前导列的索引。

对于唯一的无前缀索引，它必须包含分区键的子表。

2.全局索引

全局分区索引在一个索引分区中包含来自多个表分区的键值。在创建全局分区索引时，必须定义分区键的范围和值。全局索引只能是B树索引。oracle默认不会维护全局索引。

如果一个全局索引将被对等分区，就应该把它创建为一个本地索引。

oracle不知道无前缀的全局索引。

8.位图连接索引

位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引可以提高连接维度表和事实表的查询的性能。

create bitmap index fact_dim_col_idx on fact(dim.descr_col)

from fact，dim where fact.joincol=dim.joincol;

位图连接索引的使用是有限制的:只可以索引维度表中的列。用于连接的列必须是维度表中的主键或有唯一约束的列;如果是复合主键，就必须在连接中使用所有的索引列。不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引。

9.快速重建索引

alter index cust_idx1 rebuild parallel tablespace cust_tb1spc1 storage (pctinccrease 0);

使用rebuild 选项，可以使用已有索引而不是表来快速重建索引。执行这个操作时要有足够的空间来保存所有的索引。

10.在线重建索引

create index index_name on table(col1，。。。。) online;

alter index index_name rebuild online;

rebuild online选项，以允许对表或分区进行DML操作，但不能对位图索引指定rebuild online。对那些强制关联完整性约束的索引也一样。