不使用索引访问时的基于函数的索引

Question

我第一次使用基于函数的索引和用户定义函数，但当索引无法使用时遇到了性能问题。 在内部，基于函数的索引似乎会生成一个隐藏的表列（类型为varchar2（4000），因为我的函数返回varchar2），并对其进行索引。当索引被使用时，这很好用，但有时我们必须使用该函数作为过滤器进行全表扫描，在这种情况下，我看到性能降低了6倍。在这种情况下，Oracle似乎不使用隐藏列，而是为每行重新计算函数，使查询成为CPU绑定而不是IO绑定。 有没有办法让Oracle也使用那个隐藏列进行过滤呢？我想知道是否缺少某些重写选项或类似的东西。 如果没有，我将不得不自己定义该列，并使用触发器来保持其最新状态。我更喜欢使用基于函数的索引以实现透明和更轻松的维护。

- Chris

---

似乎有些棘手，因为“隐藏列”并未存储在表本身中，而是构成索引的一部分。因此，在进行表扫描时无法使用它。您需要让Oracle通过索引（或至少以某种方式加入）来获取预先计算的数据。 - Thilo

如果该列实际上不存在，那么很明显我不能两全其美。我刚刚查看了dba_tab_cols并认为有一个“真正的”，只是隐藏的列。如果它只是用于构建索引的虚拟结构，那么这回答了我的问题 - 我需要一个真正的列进行扫描。 - Chris

好的，它是真实存在的，因为它以实际的形式存在于索引中。与虚拟列相反，虚拟列只是一个定义，并在运行时计算数据，就像视图中的派生列一样。 - Thilo

是的，我意识到了这一点，这就是为什么看到那个SYS_NC....$列让我感到惊讶的原因 - 我错误地认为它在表中。那我就用列+触发器的方式去做吧。谢谢！ - Chris

Answer 1

您正在使用哪个版本的Oracle？如果是11g，您应该尝试使用虚拟列。这是一个从表达式或字面值派生出值的列。它们作为表的一部分定义，因此在表DESC中具有可见性（不像基于函数的索引）。我们可以在虚拟列上建立索引。它们会自动维护，无需触发器。因此，您可以使用与函数索引相同的表达式向表中添加虚拟列，例如：

create table t23
   (id number
    , col_a varchar2(10)
    , vcol_a as (upper(substr(col_a, 1, 1)))
  )
/

请注意，我们无法插入或更新虚拟列。因此，您需要指定插入语句的投影：

insert into t23 (id, col_a) values (1, 'this is a test');

然后您可以在虚拟列上构建常规索引：

create index t23_vc_i on t23(vcol_a)
/

不要忘记删除基于函数的索引！

- APC

---

不幸的是，我们的大多数客户仍在使用10g。但还是感谢您提醒我这个功能。我喜欢我可以为这个列指定一个类型，因为我发现函数索引隐式使用的varchar2（4000）有点问题（而且我认为substr解决方法也太靠不住了）。 - Chris

虚拟列不会被具体化，所以它也是即时计算的，并且 CPU 瓶颈不会得到缓解。在其上建立索引相当于现在已经存在的函数索引。 - Thilo

Answer 2

函数索引无法在表扫描中通用使用。

我在问题中做出的假设，即“内部，函数索引似乎会生成一个隐藏的表列...”，是错误的：函数的结果并没有存储在表列中，而只存储在索引中。

因此，除非有一种方法可以在执行扫描时访问索引（我能想到的唯一方法是如果它是以关键列开头的组合索引），否则预计算的函数结果无法使用。

11g的“虚拟列”功能也没有帮助，因为该列未存储在表中，而是动态计算的，类似于在视图中使用函数。

总之：如果无法排除表扫描，并且您的函数调用很耗费资源（慢），请使用真实列与“插入或更新之前”触发器相结合。函数索引行不通。

（注：添加此答案是因为我不想让这个问题保持未回答状态。答案归thilo所有，他指出该列从未被实现）。

- Chris