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.

PS我会推荐每个答案，所以请保持他们的到来。

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.

假设有少量记录得到更新，1选项是每天刷新索引。 （并在发生时记录）

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屋！