时间戳

首页 > 解决方案 > 函数索引在与其他运算符一起使用的 oracle 中不起作用

oracle - 函数索引在与其他运算符一起使用的 oracle 中不起作用

问题描述

您假设这个简单的查询：

select name, code 
  from item 
 where length(code) > 5

由于避免完全访问表，通过以下命令在长度（代码）上有一个函数索引：

create index index_len_code on item(length(code));

优化器检测索引并使用它（INDEX RANGE SCAN）。尽管如此，优化器并未检测到以下查询的上述索引：

select i.name, i.code
  from item i, item ii
 where length(i.code) - length(ii.code) > 0

当我看到执行计划时，它是访问全表，而不是索引范围扫描，而索引存在于长度（代码）上。

哪里错了，哪里错了？

标签： oracleindexingquery-performance

解决方案

如果您有一个EMP带有 column 的表HIREDATE，并且该列已编入索引，那么优化器可能会选择使用索引来访问具有类似条件的查询中的表

... HIREDATE >= ADD_MONTHS(SYSDATE, -12)

查找过去 12 个月内雇用的员工。

但是，HIREDATE必须单独在左侧。如果你添加或减去几个月或几天，或者如果你将它包装在一个函数调用中，如ADD_MONTHS，则不能使用索引。优化器不会执行琐碎的算术操作来将条件转换为HIREDATE本身必须满足不等式的条件。

在您的第二个查询中也发生了同样的情况。如果您将条件更改为

... length(i.code) > length(ii.code)

然后优化器可以使用基于函数的索引length(code)。但即使在您的第一个查询中，如果您将条件更改为

... length(code) - 5 > 0

不会使用索引，因为这不是length(code). 同样，优化器不够聪明，无法执行微不足道的代数操作，无法将其重写为length(code)自身不等式的形式。

推荐阅读