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