我在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'
它工作正常。执行计划包含正确使用了我的新索引（且高度选择性）。
但是，当我将绑定变量用于正则表达式模式时，它将不再起作用。
SELECT * FROM MY_TABLE
WHERE UPPER(REGEXP_REPLACE(,:pattern, ''))='123'
模式变量包含恒定的模式，但是无论如何，优化器都会拒绝使用索引。不知何故，Oracle的绑定窥视无法开始。
不幸的是，我不可能不使用文字而不是绑定变量，因为我的应用程序使用Hibernate来生成SQL。提示也没有解决。
我不是Oracle专家-有什么方法可以使Oracle“了解”我的绑定变量并使用索引？在文档中没有找到与我的主题相关的任何东西，除了我实际上可以为模式使用绑定变量这一事实。
任何帮助都将受到高度赞赏。
最佳答案
尽管它不能回答为什么绑定变量会停止使用索引，或者为什么会忽略提示（我已进行了验证，但是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);
然后，查询将使用该索引，因为无需担心绑定，也无需传递额外的（常量）值：
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会正确处理它。我不明白为什么不这样做，但我不使用它...
关于java - Oracle不会对具有绑定(bind)变量的regex_replace使用基于函数的索引，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22614943/