Oracle多分支机构SUM查询性能优化求助：提升SELECT语句速度

嘿，我来帮你搞定这个Oracle查询的性能问题！单个分支查询快，一用IN子查询查多个分支就变慢，大概率是执行计划选择或者索引利用的问题，给你几个实用的优化方案：

1. 把IN子查询替换为JOIN关联 #

IN子查询在某些场景下，Oracle优化器可能会选择低效的执行路径（比如嵌套循环多次扫描主表），换成JOIN关联通常能让优化器更好地选择哈希连接或合并连接，提升效率。注意要给city的子查询加DISTINCT，避免关联后数据重复导致sum计算错误：

SELECT t.FILIAL_CODE, sum(t.sum_eqv)/100 AS summa 
FROM table t
JOIN (SELECT DISTINCT code FROM city WHERE region = '26') c
  ON t.FILIAL_CODE = c.code
WHERE substr(t.acc,1,5) = '65434' 
  AND cast(substr(t.acc,18,3) as integer) >= 600 
  AND cast(substr(t.account_co,18,3) as integer)<=607 
  AND t.o_day >= to_date('01.12.2019', 'DD.MM.YYYY') 
  AND t.o_day < to_date('08.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY 
GROUP BY t.FILIAL_CODE;

2. 优化列上的函数调用，添加合适的索引 #

你当前的WHERE条件里，对acc、account_co列使用了substr+cast函数，这会导致Oracle无法直接利用普通索引，只能走全表扫描。可以创建函数索引来匹配这些过滤条件：

• 针对substr(acc,1,5)的前缀匹配：

  CREATE INDEX idx_acc_prefix ON table(substr(acc,1,5));
  

• 针对cast(substr(acc,18,3) as integer)的数值过滤：

  CREATE INDEX idx_acc_suffix_num ON table(cast(substr(acc,18,3) as integer));
  

• 针对cast(substr(account_co,18,3) as integer)的数值过滤：

  CREATE INDEX idx_accountco_suffix_num ON table(cast(substr(account_co,18,3) as integer));
  

• 另外，o_day是范围查询，建议单独加索引：

  CREATE INDEX idx_o_day ON table(o_day);
  

如果想进一步优化，可以创建覆盖索引，把过滤条件和聚合用到的字段都包含进去，避免回表查询：

CREATE INDEX idx_table_composite ON table(
  FILIAL_CODE, 
  substr(acc,1,5), 
  cast(substr(acc,18,3) as integer), 
  cast(substr(account_co,18,3) as integer), 
  o_day, 
  sum_eqv
);

3. 提前预存子查询结果（临时表/CTE） #

如果city表中region='26'的结果集很大，或者city表数据频繁变动，可以先把符合条件的分支机构代码预存起来：

用临时表： #

-- 创建临时表（会话级，退出自动清空）
CREATE GLOBAL TEMPORARY TABLE temp_city_codes AS
SELECT DISTINCT code FROM city WHERE region = '26';

-- 关联临时表查询
SELECT t.FILIAL_CODE, sum(t.sum_eqv)/100 AS summa 
FROM table t
JOIN temp_city_codes c ON t.FILIAL_CODE = c.code
WHERE substr(t.acc,1,5) = '65434' 
  AND cast(substr(t.acc,18,3) as integer) >= 600 
  AND cast(substr(t.account_co,18,3) as integer)<=607 
  AND t.o_day >= to_date('01.12.2019', 'DD.MM.YYYY') 
  AND t.o_day < to_date('08.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY 
GROUP BY t.FILIAL_CODE;

用CTE（Oracle 12c+推荐）： #

WITH city_codes AS (
  SELECT DISTINCT code FROM city WHERE region = '26'
)
SELECT t.FILIAL_CODE, sum(t.sum_eqv)/100 AS summa 
FROM table t
JOIN city_codes c ON t.FILIAL_CODE = c.code
WHERE substr(t.acc,1,5) = '65434' 
  AND cast(substr(t.acc,18,3) as integer) >= 600 
  AND cast(substr(t.account_co,18,3) as integer)<=607 
  AND t.o_day >= to_date('01.12.2019', 'DD.MM.YYYY') 
  AND t.o_day < to_date('08.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY 
GROUP BY t.FILIAL_CODE;

4. 检查并更新统计信息 #

Oracle优化器依赖准确的表统计信息来生成最优执行计划，如果统计信息过时，可能会导致优化器做出错误的选择。可以手动收集统计信息：

-- 收集主表的统计信息，替换成你的用户名
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'your_username', TABNAME => 'table');
-- 收集city表的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'your_username', TABNAME => 'city');

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