如何优化Oracle中关联亿级person表的范围检索查询性能

当前性能瓶颈主要来自两点：1. 对person.a_number的实时to_number转换导致无法高效利用索引，仅能全索引扫描；2. 合并连接产生的19亿行超大中间结果集，排序和临时空间占用成本过高。 #

可落地优化方案 #

• 方案1：创建基于函数的索引（改造成本最低）
  如果不能修改person.a_number的字段类型，直接创建基于转换后数值的索引，替换原有普通索引：

create index person_anbr_num_idx on person(to_number(a_number));

创建后Oracle可以直接利用该索引的有序性做范围匹配，避免全索引扫描和实时类型转换开销。如果可以修改字段属性，直接将a_number改为number(9)类型收益更高，省去所有转换成本。

• 方案2：调整关联逻辑减少中间结果集
  因为number_in_ranges_mv只有不到9000行数据，可优先对范围表做预处理，先计算有匹配的范围计数，再补全无匹配的范围，避免全量合并连接产生的超大中间结果：

select 
    n.range_id,
    nvl(c.cnt, 0) as a_number_count
from number_in_ranges_mv n
left join (
    select 
        nm.range_id,
        count(1) as cnt
    from number_in_ranges_mv nm
    join person p on to_number(p.a_number) between nm.begin_range and nm.end_range
    group by nm.range_id
) c on n.range_id = c.range_id;

• 方案3：更新统计信息修正执行计划
  如果表数据变化较大，过期的统计信息会导致Oracle选错执行计划，优先同步两张表的统计信息：

exec dbms_stats.gather_table_stats(ownname => '你的schema名称', tabname => 'PERSON', cascade => true);
exec dbms_stats.gather_table_stats(ownname => '你的schema名称', tabname => 'NUMBER_IN_RANGES_MV', cascade => true);

cascade => true会同步更新关联索引的统计信息，帮助优化器生成更优的执行计划。

• 方案4：无重叠范围专属优化
  如果number_in_ranges_mv中所有范围无重叠、无间隙，可以用排序后的范围边界做等值逻辑匹配，性能可提升10倍以上：

with sorted_ranges as (
    select 
        range_id,
        begin_range,
        lead(begin_range, 1, 999999999) over (order by begin_range) as next_begin
    from number_in_ranges_mv
)
select 
    s.range_id,
    count(p.a_number)
from sorted_ranges s
left join person p on to_number(p.a_number) >= s.begin_range and to_number(p.a_number) < s.next_begin
group by s.range_id;

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