<legend id='2PTmj'><style id='2PTmj'><dir id='2PTmj'><q id='2PTmj'></q></dir></style></legend>
      <i id='2PTmj'><tr id='2PTmj'><dt id='2PTmj'><q id='2PTmj'><span id='2PTmj'><b id='2PTmj'><form id='2PTmj'><ins id='2PTmj'></ins><ul id='2PTmj'></ul><sub id='2PTmj'></sub></form><legend id='2PTmj'></legend><bdo id='2PTmj'><pre id='2PTmj'><center id='2PTmj'></center></pre></bdo></b><th id='2PTmj'></th></span></q></dt></tr></i><div id='2PTmj'><tfoot id='2PTmj'></tfoot><dl id='2PTmj'><fieldset id='2PTmj'></fieldset></dl></div>

      1. <tfoot id='2PTmj'></tfoot>

        <small id='2PTmj'></small><noframes id='2PTmj'>

          <bdo id='2PTmj'></bdo><ul id='2PTmj'></ul>
      2. LIKE '%...%' 通配符查询的 PL/SQL 性能调优

        PL/SQL Performance Tuning for LIKE #39;%...%#39; Wildcard Queries(LIKE %...% 通配符查询的 PL/SQL 性能调优)
          • <legend id='T5tIr'><style id='T5tIr'><dir id='T5tIr'><q id='T5tIr'></q></dir></style></legend>

                <bdo id='T5tIr'></bdo><ul id='T5tIr'></ul>
                <tfoot id='T5tIr'></tfoot>
              • <i id='T5tIr'><tr id='T5tIr'><dt id='T5tIr'><q id='T5tIr'><span id='T5tIr'><b id='T5tIr'><form id='T5tIr'><ins id='T5tIr'></ins><ul id='T5tIr'></ul><sub id='T5tIr'></sub></form><legend id='T5tIr'></legend><bdo id='T5tIr'><pre id='T5tIr'><center id='T5tIr'></center></pre></bdo></b><th id='T5tIr'></th></span></q></dt></tr></i><div id='T5tIr'><tfoot id='T5tIr'></tfoot><dl id='T5tIr'><fieldset id='T5tIr'></fieldset></dl></div>

                    <tbody id='T5tIr'></tbody>

                  <small id='T5tIr'></small><noframes id='T5tIr'>

                  本文介绍了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
                  

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

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Accessing another user#39;s table within an Oracle Stored Procedure(在 Oracle 存储过程中访问另一个用户的表)
                  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)(超出最大存储过程、函数、触发器或视图嵌套级别(限制 32))
                  How to View Oracle Stored Procedure using SQLPlus?(如何使用 SQLPlus 查看 Oracle 存储过程?)
                  How to debug stored procedure in VS 2015?(如何在 VS 2015 中调试存储过程?)
                  How to Pass Java List of Objects to Oracle Stored Procedure Using MyBatis?(如何使用 MyBatis 将 Java 对象列表传递给 Oracle 存储过程?)
                  Set the variable result, from query(设置变量结果,来自查询)
                  • <small id='YQS1E'></small><noframes id='YQS1E'>

                      <tbody id='YQS1E'></tbody>
                  • <i id='YQS1E'><tr id='YQS1E'><dt id='YQS1E'><q id='YQS1E'><span id='YQS1E'><b id='YQS1E'><form id='YQS1E'><ins id='YQS1E'></ins><ul id='YQS1E'></ul><sub id='YQS1E'></sub></form><legend id='YQS1E'></legend><bdo id='YQS1E'><pre id='YQS1E'><center id='YQS1E'></center></pre></bdo></b><th id='YQS1E'></th></span></q></dt></tr></i><div id='YQS1E'><tfoot id='YQS1E'></tfoot><dl id='YQS1E'><fieldset id='YQS1E'></fieldset></dl></div>
                    <tfoot id='YQS1E'></tfoot>

                    <legend id='YQS1E'><style id='YQS1E'><dir id='YQS1E'><q id='YQS1E'></q></dir></style></legend>

                        • <bdo id='YQS1E'></bdo><ul id='YQS1E'></ul>