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语句