Oracle SQL Developer含CTE/关联/子查询的慢查询优化求助

需求背景 #

原有需求 #

从表a中提取tin和reg_num组合行数大于1的所有列数据，原查询使用窗口函数COUNT(*) OVER()实现，可正常运行：

with cte as (
    select
        tin, 
        reg_num, 
        name,
        fullname, 
        syscode, 
               ...
        count(*) over(partition by tin, reg_num order by tin, reg_num) as part_condition
from a
where 1=1
    and tin is not null
    and reg_num is not null
    and (isbranch = 0 or isbranch is null)
    and (isresident = 0 or countrycode = 'TR')
    and length(kpp) in (13, 15)
    and (isbranch = 0 or isbranch is null)
    and regexp_replace(lower(name), '[^a-zа-я0-9]+', '') is not null
    and regexp_replace(lower(fullname), '[^a-zа-я0-9]+', '') is not null
    and (
           lower(name) not like '%pred%'
           and lower(name) not like '%области%'
<and many other "not like" clauses here both for name and fullname>)
order by tin, reg_num)
select * from cte where part_condition > 1 order by tin, reg_num 

新增需求 #

在原有结果基础上，筛选出tin-reg_num组合中**至少有一个syscode不在表b（仅含唯一syscode列，共100万行）**的数据；若某组合的所有syscode均在表b中，则排除该组合。

当前问题 #

新增需求后的查询执行极慢，当前使用的查询语句如下：

with cte1 as (
    select a.*, concat(a.tin, a.reg_num) as tin_rn
    from a
    where concat(tin, reg_num) in 
        (select concat(tin, reg_num) as tin_rn from a left join b on a.syscode = b.syscode
        where b.syscode is null)
),            -- returns tin-reg_num combos that have at least one syscode that's not in b-table
cte2 as (
    tin, 
    reg_num, 
    name,
    fullname, 
    syscode, 
        ....
    count(*) over(partition by tin, reg_num order by tin, reg_num) as part_condition
from cte1
where 1=1
    and tin is not null
    and reg_num is not null
    and (isbranch = 0 or isbranch is null)
    and (isresident = 0 or countrycode = 'TR')
    and length(kpp) in (13, 15)
    and (isbranch = 0 or isbranch is null)
    and regexp_replace(lower(name), '[^a-zа-я0-9]+', '') is not null
    and regexp_replace(lower(fullname), '[^a-zа-я0-9]+', '') is not null
    and (
           lower(name) not like '%pred%'
           and lower(name) not like '%области%'
<and many other "not like" clauses here both for name and fullname>)
)        -- returns all columns with tin-reg_num combos that have at least one syscode that's not in b-table
--with number of rows (for each combo)
select * from cte2 where part_condition > 1    -- filtering by number of rows

优化方案 #

1. 避免使用concat关联组合键 #

concat(tin, reg_num)会破坏索引可用性，改用多列IN子句直接匹配组合键，避免字符串拼接的性能损耗：

-- 替换原cte1中的IN子句逻辑
select tin, reg_num
from a
left join b on a.syscode = b.syscode
where b.syscode is null

在主查询中直接用(tin, reg_num) in (...)，而非拼接字符串。

2. 提前过滤数据，减少CTE处理量 #

将原查询中的过滤条件提前到首个CTE，避免先全表关联再过滤，大幅减少后续步骤的数据处理量：

with cte_filtered as (
    select *
    from a
    where tin is not null
      and reg_num is not null
      and (isbranch = 0 or isbranch is null)
      and (isresident = 0 or countrycode = 'TR')
      and length(kpp) in (13, 15)
      and regexp_replace(lower(name), '[^a-zа-я0-9]+', '') is not null
      and regexp_replace(lower(fullname), '[^a-zа-я0-9]+', '') is not null
      and (
           lower(name) not like '%pred%'
           and lower(name) not like '%области%'
           -- 其他not like条件
      )
),
cte_target_combos as (
    select distinct tin, reg_num
    from cte_filtered
    left join b on cte_filtered.syscode = b.syscode
    where b.syscode is null
),
cte_final as (
    select 
        *,
        count(*) over(partition by tin, reg_num) as part_condition
    from cte_filtered
    where (tin, reg_num) in (select tin, reg_num from cte_target_combos)
)
select * from cte_final where part_condition > 1 order by tin, reg_num;

3. 简化窗口函数 #

去掉窗口函数中的order by tin, reg_num，因为count(*) over(partition by tin, reg_num)仅需统计分组内行数，不需要排序，排序会额外消耗CPU和内存资源：

count(*) over(partition by tin, reg_num) as part_condition

4. 索引优化 #

• 为表a创建复合覆盖索引，包含查询用到的过滤、分组、关联列，减少回表查询：

  create index idx_a_tin_reg_sys on a(tin, reg_num, syscode) include(isbranch, isresident, countrycode, kpp, name, fullname);
  

• 确保表b的syscode列有唯一索引（或主键），提升关联匹配速度：

  create unique index idx_b_syscode on b(syscode);
  

5. 改用EXISTS替代IN子句 #

对于大型数据集，EXISTS的执行效率通常优于IN，修改组合筛选逻辑：

cte_final as (
    select 
        *,
        count(*) over(partition by tin, reg_num) as part_condition
    from cte_filtered cf
    where exists (
        select 1
        from cte_filtered cf2
        left join b on cf2.syscode = b.syscode
        where cf2.tin = cf.tin
          and cf2.reg_num = cf.reg_num
          and b.syscode is null
    )
)

6. 预计算正则表达式结果 #

如果name和fullname的正则过滤是高频操作，可在表a中新增虚拟计算列，预存正则处理结果并创建索引，避免重复计算：

alter table a add name_cleaned as (regexp_replace(lower(name), '[^a-zа-я0-9]+', '')) virtual;
alter table a add fullname_cleaned as (regexp_replace(lower(fullname), '[^a-zа-я0-9]+', '')) virtual;
create index idx_a_name_cleaned on a(name_cleaned);
create index idx_a_fullname_cleaned on a(fullname_cleaned);

之后将查询中的正则判断改为name_cleaned is not null和fullname_cleaned is not null。

内容的提问来源于stack exchange，提问作者amateur