LIKE '%...%' 通配符查询的 PL/SQL 性能调优
我们使用的是 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
相关文章