Oracle索引优化相关_oracle索引优化

索引

需积分: 16 136 浏览量更新于2023-06-05 评论收藏 53KB DOCX 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源评论

资源推荐

Oracle 数据库经典优化之索引原理篇

Written by kinggod03 on Jul-3-09 10:28am2009-07-02T19:28:09

From:kinggod03.blog.163.com

http://www.zimbio.com/Oracle+database/articles/QOT8NgrAmYU/Oracle

开发技术 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　0SORT（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　0SORT（AGGREGATE）

　　　2　1　INDEX（SKIP SCAN） OF 'SKIP1'（NON-UNIQUE）

　　　Statistics 21 consistent gets 17 physical　 reads

10、索引的类型：B-树索引位图索引 HASH 索引索引编排表反转键索引基于函数的索引分区索引本地

和全局索引

Oracle 中建立索引并强制优化器

http://www.bitscn.com/pdb/oracle/200709/109361_2.html

当 WHERE 子句对某一列使用函数时，除非利用这个简单的技术强制索引，否则 Oracle 优化器不能在查

询中使用索引。

通常情况下，如果在 WHERE 子句中不使用诸如 UPPER、REPLACE 或 SUBSTRD 等函数，就不能对指定列

建立特定的条件。但如果使用了这些函数，则会出现一个问题：这些函数会阻碍 Oracle 优化器对列使用

索引，因而与采用索引的情况相比较，查询会花费更多的时间。

庆幸的是，如果在使用函数的这些列中包含了字符型数据，可以用这样一种方法修改查询语句，以达到强

制性使用索引，更有效地运行查询。这篇文章介绍了涉及的技术，并说明了在两种典型情况下怎样实现。

大小写混合情况

在讨论由于函数修改了列的内容，如何强制使用索引前，让我们首先看看为什么 Oracle 优化器在这种情

况下不能使用索引。假定我们要搜寻包含了大小写混合的数据，如 ADDRESS 表的 NAME 列。因为数据是

用户输入的，我们无法使用已经统一改为大写的数据。为了找到每一个名为 john 的地址，我们使用包含

了 UPPER 子句的查询语句。如下所示：

SQL> select address from address where upper(name) like 'JOHN';

在运行这个查询语句前，如果我们运行了命令"set autotrace on", 将会得到下列结果，其中包含了

执行过程：

剩余21页未读，继续阅读

alphapeipei

粉丝: 0
资源: 1

会员权益专享

Oracle索引优化相关

评论0

会员权益专享

最新资源

Oracle索引优化相关

评论0

Java对Oracle索引的使用方法

oracle数据库优化

oracle数据库优化器的工作原理

Oracle建立索引后没走

oracle索引的使用

oracle数据库优化器的知识

oracle sql优化

oracle left join 不走索引

oracle 查询优化

oracle 慢sql优化相关步骤

Oracle慢查询优化

Oracle查询索引

Oracle SQL优化

oracle left join 优化

oracle的sql优化

oracle inner join查询优化

oracle查询千万数据优化

服务器故障诊断及排除.ppt

服务器的防火墙.doc

会员权益专享

最新资源