限制索引 Oracle

2022-03-06 06:26:07

在SQL中有很多陷阱会使一些索引无法使用，这就造成了虽然我们在需要的列上建了索引但却没有用到索引。下面列出一些常见的问题：

一、使用不等于操作符（<>、!=）

1、下面的查询即使在je_line_id列有一个索引，查询语句仍然执行一次全表扫描。

SELECT * FROM cux_gl_je_lines l WHERE l.je_line_id <> 1234;

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

SELECT *
  FROM cux_gl_je_lines l
 WHERE l.je_line_id < 1234
    OR l.je_line_id > 1234;

通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。

二、使用IS NULL或IS NOT NULL

使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOTNULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

三、使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引：accounting_date（只要它不是基于函数的索引）：

SELECT *
  FROM cux_gl_je_headers h
 WHERE trunc(h.accounting_date) = ‘26-FEB-13‘;

把上面的语句改成下面的语句，这样就可以通过索引进行查找。

SELECT *
  FROM cux_gl_je_headers h
 WHERE h.accounting_date = to_date(‘2013-02-26‘, ‘YYYY/MM/DD‘);

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

也是比较难于发现的性能问题之一。注意下面查询的例子，source_header_id是一个VARCHAR2类型,在source_header_id字段上有索引。

下面的语句将执行全表扫描：

SELECT * FROM cux_gl_je_headers h WHERE h.source_header_id = 10145;

Oracle可以自动把where子句变成to_number(source_header_id)=10145，这样就限制了索引的使用,改成下面的查询就可以使用索引：

SELECT * FROM cux_gl_je_headers h WHERE h.source_header_id = ‘10145‘;

不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行ExplainPlan也不能让您明白为什么做了一次“全表扫描”。

码农公寓