针对“%.%”的通配符查询的PL/SQL性能优化

Question

我们正在使用Oracle 11g数据库。

您可能知道也可能不知道，如果在字符串前面使用带有"%“的通配符查询，则列索引不使用，并且正在进行全表扫描。

对于如何改进这类查询，似乎没有明确的建议，但也许您可以从如何优化以下查询的经验中分享一些有价值的信息：

SELECT * 
  FROM myTable 
 WHERE UPPER(CustomerName) like '%ABC%' 
    OR UPPER(IndemnifierOneName) like '%ABC%' 
    OR UPPER(IndemnifierTwoName) like '%ABC%';

...where所有3列都是varchar2(100)类型，ABC是变量输入参数的值。

@所有建议的CONTEX索引，请注意我的数据每天的任何时候都会被更新，这个索引需要重新同步，因此不是是150万行表的一个很好的选择，对不起。

我会把所有的答案都投出来，所以请不要让他们来。

Answer 1

如前所述，可以将ctx上下文索引添加到名称列中。

假设有少量记录被更新，一个选项是每天刷新索引。(并记录发生的时间)

然后向正在搜索的表中添加一个最新更新日期列&索引。

应该可以扫描您的ctx索引，查找大部分旧的未更改的数据，并使用以下方法从更新后的数据中选择一小部分：

WHERE (lastupdated<lastrefresh AND contains(name,'%ABC%')) 
   OR (lastupdated>lastrefresh AND name like '%ABC%')

注意:您可能会发现您的查询计划有点疯狂(很多位图转换到行ids)，在这种情况下，将OR的两个部分分割成一个UNION查询。e.g

SELECT id FROM mytable   
    WHERE 
    (lastupdate>lastrefresh and name LIKE '%ABC%')
    UNION ALL
    SELECT id FROM mytable   
    WHERE lastupdate<lastrefresh and CONTAINS(name, '%ABC%', 1) > 0

Answer 2

唯一的优化是不要使用这种类型的查询，而是使用数据库平台的本地功能：

见Oracle：http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html

SQL Server相关问题的常见答案是全文搜索。很高兴看到甲骨文有一样好或更好的东西。

Answer 3

UPPER()在任何情况下都会删除索引，请考虑使用regexp。初始的%可能避免正常的索引扫描，但并不总是导致全表扫描，而是变成完整索引扫描，这比FTS更快。

我认为'ABC'是可变的。如果不是，函数索引就是要走的路。