Oracle 表连接查询条件，like 前后% 优化方案【已解决】

Oracle 表连接查询条件，like 前后% 优化方案【已解决】

【1】背景

• 业务涉及两个表，连接查询，但是连接条件中有要用到 like 模糊查询，而且是前后带 % 的那种（一个表字段中的数据 可能由 另一个表的字段多行拼接而成，详见表数据样例）。
• 查询了网上的各种优化方案，没有很好的提升速度，最终想到解决办法，因此记录为念。

【2】表数据量 与 表数据样例 展示：

• 数据量：
  表格数据背景如下，分别由14万条数据，和11万条数据。
  
• 表数据样例
  在一个表中以分号分割 与 另一个表中的字段匹配
  
  

【3】sql执行计划对比（PL/SQL 选中sql语句 按 F5 可调出）

【3.1】方案一（原始语句）：like + %（查询十几分钟）

使用最原始的实现方式 like 和 前后‘%’ 执行计划

【3.2】方案二（优化）：instr（没好到哪里去）

使用网上多数优化方法instr的执行计划

【3.3】方案三（解决方案）：字符分割后查询，避免模糊搜索导致的全表匹配（执行20多秒）

使用字符分割后进行表连接 的 执行计划

【4】结论【本背景下：‘字段分割’法 > ‘instr’法 > like+’%’】

• 对比可以看出 like执行效率最低，特别是这种前后带‘%’的；
• instr可以优化like，但对于前后带‘%’的like语句效果不显著，【参考文献4】
• 若需要模糊查询的字段由多个同类数据拼接，可考虑拆分后再连接；

【5】附：文中的SQL脚本

【like语句的sql】和【instr优化的sql】

select t.派车单编号,t1.单据编号
    from TA_TRUCKINGORDER t
    inner Join TA_WEIGHRECORD t1
    on instr(t1.单据编号,t.派车单编号) >0;
    --on t1.单据编号  like '%'||t.派车单编号||'%';

【字段分割优化法的sql】

select t.派车单编号, t2.派车单号
  from TA_TRUCKINGORDER t
  inner Join (select t1.空车称重时间,--将称重表的单据编号字段拆分成多行
                    regexp_substr(t1.单据编号, '[^;]+', 1, level) as 派车单号
               from ta_weighrecord t1
             connect by t1.称重id = prior t1.称重id
                    and prior dbms_random.value is not null
                    and level <= REGEXP_COUNT(t1.单据编号, '[^;]+')) t2
    on t2.派车单号 = t.派车单编号

【6】参考文章

最终采用的方法：
1、oracle如何拆分以特定分隔的字段为多行

试过但我不适用的方法：
2、Oracle模糊查询优化小技巧
3、Oracle like语句优化
4、【oracle优化】优化含LIKE的SQL语句