oracle使用了索引但是查询性能没有改善(oracle的索引陷阱)

原创 2006年06月05日 11:47:00

oracle的索引陷阱

 文/Ray

一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。

oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。

下面是一些常见的索引限制问题。

1、使用不等于操作符(<>, !=)

下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描

select * from dept where staff_num <> 1000;

但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?

有!

通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。

select * from dept shere staff_num < 1000 or dept_id > 1000;

2、使用 is null 或 is not null

使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。

解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)

 3、使用函数

如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引:

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

4、比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

select * from dept where dept_id = '900198';

5、使用like子句

使用like子句查询时,数据需要把所有的记录都遍历来进行判断,索引不能发挥作用,这种情况也要尽量避免。

为什么有时ORACLE数据库不用索引来查找数据

为什么有时ORACLE数据库不用索引来查找数据? (作者:福州电信局计算机中心 李炯 2000年05月15日 18:17)    当你运用SQL语言,向数据库发布一条查询语句时,ORACLE将...
  • sunhuiliang85
  • sunhuiliang85
  • 2014年04月24日 20:23
  • 1102

oracle 不走索引的原因

create table tb2 as select * from emp; alter table tb2 modify empno number(4) not null; 翻到20W行 ...
  • wacthamu
  • wacthamu
  • 2013年02月01日 13:46
  • 12165

查看Oracle的表中有哪些索引(用user_indexes和user_ind_columns)

查看Oracle中表的索引是否存在 分类: 数据库 2011-03-24 19:30 7173人阅读 评论(1) 收藏 举报 oracleconstraintstableus...
  • haiross
  • haiross
  • 2013年10月24日 10:20
  • 35450

oracle 创建、使用索引和判断索引是否被使用

一、创建索引:         create index index_name on table_name(table_field); 二、修改索引:         alter index 索...
  • fygkchina
  • fygkchina
  • 2014年03月28日 10:59
  • 4292

oracle 性能优化--索引总结

索引是建立在表的一列或多个列上的辅助对象,目的是加快访问表中的数据;     Oracle存储索引的数据结构是B*树,位图索引也是如此,只不过是叶子节点不同B*数索引;     索引由根节...
  • goodhuajun
  • goodhuajun
  • 2014年09月30日 15:21
  • 2304

在Oracle中索引的使用

索引是由Oracle维护的可选结构,为数据提供快速的访问。准确地判断在什么地方需要使用索引是困难的,使用索引有利于调节检索速度。 当建立一个索引时,必须指定用于跟踪的表名以及一个或多个表列。一旦建立了...
  • ysyn1209
  • ysyn1209
  • 2016年04月22日 23:44
  • 6864

Oracle数据库之视图与索引

Oracle数据库之视图与索引1. 视图简介视图是基于一个表或多个表或视图的逻辑表,本身不包含数据,通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表,视图是存储在数据字典里的一条SELEC...
  • zhliro
  • zhliro
  • 2015年06月22日 23:44
  • 2002

ORACLE三大索引适用范围

索引,索引特性
  • u013660039
  • u013660039
  • 2015年05月18日 17:18
  • 2480

Oracle 复合索引

单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。 用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。但复合索引在数据库操作...
  • jiachangbin1989
  • jiachangbin1989
  • 2016年04月25日 17:05
  • 2875

oracle查询表 索引、唯一约束、字段等相关信息

查询表的索引情况 select t.Index_Name,t.table_name,t.column_name,i.tablespace_name,i.uniqueness from user_...
  • cuker919
  • cuker919
  • 2013年10月17日 17:14
  • 11200
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle使用了索引但是查询性能没有改善(oracle的索引陷阱)
举报原因:
原因补充:

(最多只允许输入30个字)