代码之家 › 专栏 › 技术社区 › Kevin Babcock

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

query-optimization oracle10g oracle performance sql

Kevin Babcock · 技术社区 · 15 年前

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

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

我看了一下甲骨文,但对于这么一个小的专栏来说,这似乎太过分了。但是,此表中有数百万条记录,因此查找子字符串匹配所花费的时间比我希望的要多。有更好的方法吗?

6 回复 | 直到 15 年前

cletus 15 年前

您有三个选择:

重新定义问题的最简单方法是说列必须从搜索项开始(因此先丢失),然后使用索引。

另一种方法是说搜索从单词边界开始(因此“est”将匹配“estimate”,而不是“test”)。mysql(myisam)和SQL Server有这样的匹配功能。不确定Oracle是否这样做。如果没有,您可以创建一个单词查找表来搜索,而不是列本身,您可以在触发器上填充该表。

Will Hartung 15 年前

不。

该查询是表扫描。如果v值是一个实际的单词,那么您很可能希望看到Oracle文本或一个简单的反向索引方案,您可以自己滚动。但事实上,这很可怕。

APC 15 年前

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

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

与其他类型的文本索引不同,CTXCAT索引是事务性的,因此不需要同步。这样的索引占用了大量的空间,但是您必须为改进性能付出一些代价。

akf 15 年前

您可以使用 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'。如果您在执行特殊查询时希望提高性能,这可能不是您的解决方案。

不过,正如函数名所示,还有一个额外的好处,那就是您有机会使用regex创建这个索引,它最终可能是一个强大的工具。当项目添加到表中时,而不是在搜索期间,将进行评估命中。

OMG Ponies 15 年前

你可以试试 INSTR :

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

我没有访问Oracle的权限来测试和查明它是否比使用通配符更快。

David Aldridge 15 年前

对于最一般的情况,如果您事先不知道您正在搜索的字符串,那么您希望得到的最佳访问路径是快速的完整索引扫描。您必须将精力集中在尽可能小的索引上,这当然可能有它自己的问题,如果数据的基数不是很高,也可以查看压缩的索引。