优化 Oracle 查询以查找子字符串匹配的好方法是什么？

Question

我在非分区 Oracle 表中有一列定义为 VARCHAR2(50)；该列有一个标准的 B 树索引。我想知道是否有最佳方法来查询此列以确定它是否包含给定值。这是当前的查询：

SELECT * FROM my_table m WHERE m.my_column LIKE '%'||v_value||'%';

我查看了 Oracle Text，但这对于这么小的列来说似乎有点过分了。但是，该表中有数百万条记录，因此查找子字符串匹配所花费的时间比我想要的要多。有更好的办法吗？

Answer 1

不。

该查询是表扫描。如果 v_value 是一个实际的单词，那么您可能非常想查看 Oracle Text 或您自己推出的简单倒排索引方案。但就目前情况而言，这太可怕了。

Answer 2

Oracle Text 涵盖了许多不同的方法，但并非所有方法都是重量级的。由于您的列非常小，您可以使用 CTXCAT 索引对其进行索引。

SELECT * FROM my_table m 
WHERE catsearch(m.my_column, v_value, null) > 0
/

与其他类型的文本索引不同，CTXCAT 索引是事务性的，因此不需要同步。这样的索引会消耗大量的空间，而且为了提高性能你必须付出一些代价。

了解更多信息。

Answer 3

你有三个选择：

• 忍受它；
• 使用Oracle Text 进行全文搜索；或者
• 重新定义问题，以便您可以实施更快的解决方案。

重新定义问题的最简单方法是指定列必须以搜索词开头（因此丢失第一个 %），然后它将使用索引。

另一种方法是说搜索从单词边界开始（因此“est”将匹配“estimate”，但不匹配“test”）。 MySQL (MyISAM) 和 SQL Server 都有可以进行这样的匹配的函数。不确定 Oracle 是否这样做。如果没有，您可以创建一个要搜索的单词查找表而不是列本身，并且可以在触发器上填充该表。

Answer 4

您可以使用 REGEXP_LIKE 函数在列上放置基于函数的索引。您可能需要使用 case 语句创建 fbi，以返回匹配的“1”，因为布尔返回函数在 fbi 中似乎无效。

这是一个例子。

创建索引：

CREATE INDEX regexp_like_on_myCol ON my_table (
      CASE WHEN REGEXP_LIKE(my_column, '[static exp]', 'i') 
           THEN 1
           END);

然后使用它，而不是：

SELECT * FROM my_table m WHERE m.my_column LIKE '%'||v_value||'%';

您将需要执行如下所示的查询：

SELECT * FROM my_table m WHERE (
      CASE WHEN REGEXP_LIKE(m.my_column, '[static exp]', 'i')
           THEN 1
           END) IS NOT NULL;

此方法的一个显着缺点是您需要在创建时知道您的“[static exp]”你的索引。如果您希望在执行即席查询时提高性能，那么这可能不适合您。

不过，正如函数名称所示，一个好处是您有机会使用正则表达式创建此索引，这最终可能是一个强大的工具。评估命中将在项目添加到表中时进行，而不是在搜索期间进行。

Answer 5

您可以尝试 INSTR：

...WHERE INSTR(m.my_column, v_value) > 0

我无权访问 Oracle 来测试&看看它是否比使用通配符的 LIKE 更快。

Answer 6

对于最常见的情况，您事先不知道要搜索的字符串，那么您希望的最佳访问路径是快速完整索引扫描。您必须集中精力使索引尽可能小，这当然可能有其自身的问题，并且如果数据的基数不是很高，则可以查看压缩索引。