Oracle中的索引

一、索引的分类

1. B*Tree索引
   B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时 提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的 性能。
   create index index_name on table_name(field_name,...)
   
2. 反向索引
   反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问 相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节。
   create index index_name on table_name(field_name,...) reverse
3. 降序索引
   降序索引是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用，例如在查询条件中有order by语句。
   create index index_name on table_name(field_name1 desc, field_name2 asc, ...)
4. 位图索引
   位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低基数列（即列的唯一值除以行数为一个很小的值，接近 零），例如一个"性别"列，列值有"Male"，"Female"，"Null"等3种，但一共有300万条记录，那么3/3000000约等于0，这 种情况下最适合用位图索引。
   create bitmap index index_name on table_name(field_name,...)
5. 函数索引
   基于函数的索引有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能，适用于查询条件中包含函数运算项的情况。
   create index index_name on table_name(UPPER(field_name))

      注意事项：
       创建索引后分析要索引才能起作用：

analyze table table_name compute statistics for all indexes;

或数据量大时使用 analyze table table_name estimate statistics sample 5 percent;

重建索引：

alter index idx_name rebuild nologging;

 二、分析索引使用

      要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的。

1. 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。
2. AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。
3. 普通用户需要授予SELECT_CATALOG_ROLE权限使用autotrace。
   SQL> GRANT SELECT_CATALOG_ROLE TO USER
   
4. AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

    例如：
    SQL> set autotrace on
    SQL> select * from test;

三、索引使用注意事项

1. 表未做statistics, 或者 statistics 陈旧，导致Oracle判断失误，未使用索引。
2. 索引不是越多越好。特别是大量索引即会降低性能，而且在一个sql 中，Oracle几乎不用的索引，对系统只有损害。一般一个表不要超过 5个索引。
3. 很多时候，单列索引不如复合索引有效率。但是查询条件中只有在使用到索引的前导索引时才可以使用组合索引
4. 用于多表连结的字段，加上索引会很有作用。
5. 使用外连接查询时，主表无法使用索引，因此外连接的效率要比内连接低得多。
   
6. where 子句中的字段，不应该参与任何形式的计算，才会使用索引。
7. 低基数值的列要建位图索引才有效果，如果是组合索引，则不应包含低基数值的列。
8. 在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。
9. 如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。
10. 查询条件中使用不等于操作符（<>、!=）的字段不会使用索引。通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
11. 屏蔽索引，语句的执行计划中有不良索引时，可以人为地屏蔽该索引，方法：
    数值型：在索引字段上加0，例如　　select * from emp where emp_no+0 = v_emp_no;
    字符型：在索引字段上加''，例如　　select * from tg_cdr01 where msisdn''=v_msisdn;