一.索引基本概念
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个
为了让优化器选择跳跃式扫描，可能需要在查询中像该程序清单所示的那样使用提示，提示影响了优化器，使其偏向您所指定的执行路径。对于那些有 组合索引的大型表而言，索引跳跃式扫描特性可以再前导列不在限制条件中时提供一种快速访问方法。
更多详情见请继续阅读下一页的精彩内容：