java - Oracle 不对带有绑定(bind)变量的 regex_replace 使用基于函数的索引

Question

我的 Oracle 11g 数据库中有一个表，其中一列中有非格式化文本，应在输入时显示。无论如何，用户应该能够以任何可能的格式(关于标点符号)搜索该文本。我通过在列上引入基于函数的索引来满足该要求:

CREATE INDEX "MY_REGEX_INDEX" ON "MY_TABLE" (UPPER( REGEXP_REPLACE ("MY_COLUMN",'[:punct:]','')))

当我用

查询这个索引时

SELECT * FROM MY_TABLE
WHERE UPPER(REGEXP_REPLACE(,'[:punct:]', ''))='123'

它工作正常。执行计划包含对我的新(和高度选择性)索引的正确使用。

但是当我为正则表达式模式使用绑定(bind)变量时，它不再起作用了。

SELECT * FROM MY_TABLE
WHERE UPPER(REGEXP_REPLACE(,:pattern, ''))='123'

模式变量包含常量模式，但无论如何，优化器拒绝使用任何索引。不知何故，Oracle 的绑定(bind)查看没有启动。

不幸的是，我不可能不使用文字来代替绑定(bind)变量，因为我的应用程序使用 Hibernate 来生成 SQL。提示也没有解决。

我不是 Oracle 专家 - 有没有办法让 Oracle“理解”我的绑定(bind)变量并使用索引？在文档中没有找到与我的主题相关的任何内容，除了我实际上可以为模式使用绑定(bind)变量这一事实。

非常感谢任何帮助。

Answer 1

虽然它没有回答为什么绑定(bind)变量停止使用索引，或者为什么忽略提示(我已经验证过，但 Oracle 可以自由地忽略我想的提示 - 因此得名)，你可以采取另一种方法并改用虚拟列:

drop index my_regex_index;

alter table my_table add my_regexp_column generated always
  as (upper(regexp_replace(my_column, '[[:punct:]]')));

create index my_regex_index on my_table (my_regexp_column);

然后查询将使用该索引，因为无需担心绑定(bind)，也无需传递额外(常量)值:

select * from my_table where my_regexp_column = '123';

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |   204 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TABLE       |     1 |   204 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MY_REGEX_INDEX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

当然，这假设您可以添加列，并且 Hibernate 会正确处理它。我不明白为什么不，但我不使用它...