Oracle索引技术详解

xiaoerp

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容：
[1] 基本的索引概念
    查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问 USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。
[2] 组合索引
    当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：表emp有一个组合索引键，该索引包含了empno、 ename和deptno。在Oracle9i之前除非在where之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次范围扫描。
    特别注意：在Oracle9i之前，只有在使用到索引的前导索引时才可以使用组合索引！
[3] Oracle ROWID
    通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID 本身的引用，可以在任何表中使用和指定rowid列。   
[4] 限制索引
    限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题：
   
    4.1 使用不等于操作符（<>、!=）
        下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
         select cust_Id,cust_name
         from   customers
         where  cust_rating <> 'aa';
         把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。
         select cust_Id,cust_name
         from   customers
         where  cust_rating < 'aa' or cust_rating > 'aa';
     特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
     
     4.2 使用IS NULL 或IS NOT NULL
         使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开     发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索     引，关于位图索引在稍后在详细讨论）。
     4.3 使用函数
         如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。
         下面的查询不会使用索引（只要它不是基于函数的索引）
          select empno,ename,deptno
          from   emp
          where  trunc(hiredate)='01-MAY-81';
          把上面的语句改成下面的语句，这样就可以通过索引进行查找。
          select empno,ename,deptno
          from   emp
          where  hiredate<(to_date('01-MAY-81')+0.9999);
     4.4 比较不匹配的数据类型
         比较不匹配的数据类型也是比较难于发现的性能问题之一。
         注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。
         select bank_name,address,city,state,zip
         from   banks
         where  account_number = 990354;
         Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
         select bank_name,address,city,state,zip
         from   banks
         where  account_number ='990354';
     特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一               次“全表扫描”。
[5] 选择性
    使用USER_INDEXES视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。
[6] 群集因子(Clustering Factor)
    Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。
   
[7] 二元高度(Binary height)
    索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后，可以通过查询DBA_INDEXES的B- level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除键的数目。重建索引可能会降低二元高度。
[8] 快速全局扫描
    在Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的 DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。
[9] 跳跃式扫描
    从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。下面的程序清单显示出性能的差别：
    create index skip1 on emp5(job,empno);
    index created.
    select count(*)
    from emp5
    where empno=7900;
    Elapsed:00:00:03.13
    Execution Plan
    0     SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)
    Statistics
    6826 consistent gets
    6819 physical   reads
    select /*+ index(emp5 skip1)*/ count(*)
    from emp5
    where empno=7900;
    Elapsed:00:00:00.56
    Execution Plan
    0     SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)
    Statistics
    21 consistent gets
    17 physical   reads
[10] 索引的类型
     B-树索引
     位图索引
     HASH索引
     索引编排表
     反转键索引
     基于函数的索引
     分区索引
     本地和全局索引

关于索引的一下理论
1)如果建单个索引，则只建一个索引（包括A字段即可），其他三个字段的索引在该语句执行时毫无用处
（条件子句解析时字段次序应与索引字段次序一致，可以比索引字段多或少，就是不能打乱次序）。
首索引的选择是非常重要的，应该选值最多的字段，在性能上得宜最大，称为“首索引原则”（内部名词 ）。

2)创建联合索引时，如CREATE INDEX I ON EMP(ID,NAME,DEPTNO)，
重复值多的列如DEPTNO应该往后放，重复值少的列如ID应该放在前面,这样定位记录就会更快
重复值少就会过滤掉大部分记录了
在8i中，如果想使用到符合索引，必须在where语句中引用到索引的先导列(也就是索引的第一个字段)
在9i中，Oralce提供了一种新特性:跳跃式索引扫描，Oracle声称对用户透明，可实际上如果你想使用到这个新特性，
必须添加index_ss提示
select /*+ index_ss(emp)*/* from emp where sal>2000 and deptno>9;

3)位图索引通常要少于300个完全不同的值。在对表数据做40％的查询时候。用位图索引比全表扫描要快。一般B树索引在10－20％左右
4)位图索引局限性
被索引的数据列必须是低基数的——通常要少于300个完全不同的值。
在WHERE子句里，查询绝对不能索引哪些没有包含在索引里的数据列。
更新位图连接索引所需要的代价是相当高的。从实用的角度讲，位图连接索引被抛弃，而在每天晚上进行每日批量加载任务的时候才被重建。只有对于那些在处理的时候保持只读的Oracle数据仓，位图连接索引才会起作用。
总而言之，位图连接索引会极大地提高特定数据仓查询的速度，但是其代价是在为图索引创建的时候，需要预先连接表格。