LIKE '%...%' 通配符查询的 PL/SQL 性能调优 [英] PL/SQL Performance Tuning for LIKE '%...%' Wildcard Queries

问题描述

我们使用的是 Oracle 11g 数据库.
您可能知道也可能不知道，如果您在字符串前面使用带有%"的通配符查询，则不会使用列索引，并且全表扫描发生.

We're using Oracle 11g database.
As you may or may not know, if you use wildcard query with "%" in front of the string, the column index is not being used and a full table scan is happening.

似乎没有关于如何改进此类查询的明确建议，但也许您可以分享一些关于如何优化以下查询的经验的宝贵信息:

It looks like there isn't a definitive suggestion on how to improve this kind of query, but perhaps you could share some valuable information from your experience on how to optimize the following query:

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

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

...where all 3 columns are of type varchar2(100) and ABC is a value of variable input parameter.

@All 建议 CONTEX 索引，请注意我的数据每天随时更新，此索引需要重新同步，因此它不是一个好的选择对于150 万行 的表，抱歉.​​

@All suggesting CONTEX index, please note my data gets updated any time of the day every day and this index requires re-syncing, hence it's not a good option for a table of 1.5 million rows, sorry.

附言我会为每个答案点赞，所以请让他们继续.

P.S. I'll upvote every answer, so please do keep them coming.

推荐答案

如前所述，您可以向名称列添加 ctx 上下文索引.

As already mentioned you could add a ctx context index to the name columns.

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

assuming a small number of records get updated, 1 option is to refresh your index daily. (and record when it happened)

然后添加一个 lastupdate 日期列 &正在搜索的表的索引.

then add a lastupdate date column & index to your table being searched.

应该可以扫描您的 ctx 索引以获取大部分未更改的旧数据并使用传统的 LIKE 从更新数据的一小部分中选择例如:

It should be possible to scan your ctx index for the majority of the old unchanged data and select from the small percentage of updated data using the traditonal LIKE e.g:

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

注意:在这种情况下，您可能会发现您的查询计划有点脑残(大量位图转换为行 ID)，在这种情况下将 OR 的 2 部分拆分为 UNION ALL 查询.例如

NOTE: you may find your query plan goes a little mental (lots of bitmap conversions to row ids) in that case split the 2 parts of the OR into a UNION ALL query. 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

这篇关于LIKE '%...%' 通配符查询的 PL/SQL 性能调优的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！