Oracle索引原理

时间：2011-03-07 来源：蓝色的天空III

来源：网海拾贝

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索引
     索引编排表
     反转键索引
     基于函数的索引
     分区索引
     当地和全局索引