三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

【Oracle专栏】优化慢的查询

发布时间：2026/8/12 20:42:09 三亩地 编程学习日记
【Oracle专栏】优化慢的查询

Oracle && OceanBase 相关文档，希望互相学习，共同进步

风123456789～-CSDN博客

1.背景

业务过程跑批慢，优化相关语句 和表。

2. 实验

2.1 实验说明

1）原表中255万数据

原语句：

SELECT TO_CHAR(SYSDATE,'YYYY-MM'),SYSDATE,'NHTC_INCOME_AND_EXPENDITURE', '-','COUNT_DIFF','','', D.KID || ',' || D.SOCIAL_CREDIT_CODE || ',' || D.YEAR_MONTH || ',本月：' || A.CURRENT_AMT1 || ' 与 '|| D.CURRENT_AMT1 || ',本年：' || A.SUM_AMT1|| ' 与 '||d.SUM_AMT1 , D.SOCIAL_CREDIT_CODE,'本月数、本年数与收入合计的不符(收入)','1',D.YEAR_MONTH, DECODE(SUBSTR(D.SOCIAL_CREDIT_CODE,1,3),'014',D.SOCIAL_CREDIT_CODE,''),get_seqbatch FROM (SELECT D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH,SUM(D.CURRENT_AMT1) CURRENT_AMT1,SUM(D.SUM_AMT1) SUM_AMT1 FROM NHTC_INCOME_AND_EXPENDITURE D WHERE D.DATA_FLAG = 'M' AND LENGTH(D.YEAR_MONTH) = 7 AND LENGTH(D.INCOME_SUBJECT_CODE) = 3 GROUP BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH ) A,NHTC_INCOME_AND_EXPENDITURE D WHERE D.INCOME_SUBJECT_CODE = '9990' AND A.SOCIAL_CREDIT_CODE = D.SOCIAL_CREDIT_CODE AND A.YEAR_MONTH = D.YEAR_MONTH AND (A.CURRENT_AMT1 <> D.CURRENT_AMT1 OR A.SUM_AMT1 <> D.SUM_AMT1);

说明：这个语句，执行用了将近4小时

2.2 优化分析

一般性能瓶颈：可能出现在‌全表扫描、隐式类型转换、函数导致的索引失效以及低效的连接方式‌上。

1. 核心优化点分析

A. 避免在 WHERE 子句中对列使用函数（关键）

原句中LENGTH(D.YEAR_MONTH) = 7和LENGTH(D.INCOME_SUBJECT_CODE) = 3会导致数据库无法使用YEAR_MONTH和 `INCOME_SUBJECT_CODE字段上的普通索引进行快速查找，从而引发全表扫描。

  • ‌优化方案‌：如果YEAR_MONTH格式固定（如 '2026-05'），直接使用范围查询或等值匹配；如果必须校验长度，建议添加函数基于索引（Function-Based Index），或者在数据入库时确保数据规范性，查询时去掉LENGTH判断。
B. 优化子查询与连接方式

原句使用了一个聚合子查询A并与主表D进行关联。

  • ‌问题‌：子查询A需要对NHTC_INCOME_AND_EXPENDITURE全表（或大范围）进行GROUP BY聚合，计算量大。
  • ‌优化方案‌：
    1. ‌利用窗口函数（Window Functions）‌：使用SUM() OVER()替代子查询聚合，避免多次扫描表或复杂的 Hash Join。
    2. ‌确保连接字段有索引‌：SOCIAL_CREDIT_CODE和YEAR_MONTH必须有联合索引。
C. SYSDATE 的使用

虽然SYSDATE本身开销极小，但在高并发下，频繁调用非确定性函数可能影响执行计划稳定性。

  • ‌优化方案‌：在本场景中，SYSDATE仅用于展示，对性能影响微乎其微，无需过度优化。但需注意不要在WHERE条件中对索引列做SYSDATE相关的运算（如col > SYSDATE - 1可能导致索引失效，虽此处未涉及，但需留意）。
D. DECODE 与字符串拼接

DECODE和字符串拼接 (||) 是 CPU 密集型操作，但通常在结果集较小时不是主要瓶颈。主要瓶颈在于‌如何快速找到需要拼接的那几行数据‌。

2. 索引建议

索引建议：确保以下索引存在

1‌）覆盖查询条件的复合索引‌（最重要）
CREATE INDEX IDX_NHTC_QUERY ON NHTC_INCOME_AND_EXPENDITURE ( DATA_FLAG, INCOME_SUBJECT_CODE, SOCIAL_CREDIT_CODE, YEAR_MONTH );

注意：将DATA_FLAG和INCOME_SUBJECT_CODE放在前面，因为它们是在 WHERE 中用于过滤的主要条件。

2）‌针对 LENGTH 函数的优化（可选）
CREATE INDEX IDX_NHTC_YEAR_LEN ON NHTC_INCOME_AND_EXPENDITURE (LENGTH(YEAR_MONTH));

如果YEAR_MONTH格式固定为 'YYYY-MM'，直接去掉LENGTH判断

2.3 优化处理

1.优化调整思路

根据优化建议，做以下调整：

1）去掉LENGTH(D.YEAR_MONTH) = 7，改为入库时校验

2）使用 窗口函数SUM() OVER()替代子查询聚合，避免多次扫描表或复杂的 Hash Join

这个改到条件中LENGTH(D.INCOME_SUBJECT_CODE) = 3,这个无法避免

3）增加适当索引

2.优化语句

1）优化语句1：6s
SELECT TO_CHAR(SYSDATE,'YYYY-MM'),SYSDATE,'NHTC_INCOME_AND_EXPENDITURE', -- '-','COUNT_DIFF','','', D.KID || ',' || D.SOCIAL_CREDIT_CODE || ',' || D.YEAR_MONTH || ',本月：' || D.CURRENT_AMT2 || ' 与 '|| D.CURRENT_AMT || ',本年：' || D.SUM_AMT2|| ' 与 '||D.SUM_AMT , D.SOCIAL_CREDIT_CODE,'本月数、本年数与支出合计的不符(支出)','1',D.YEAR_MONTH, DECODE(SUBSTR(D.SOCIAL_CREDIT_CODE,1,3),'014',D.SOCIAL_CREDIT_CODE,''),GET_SEQBATCH FROM ( SELECT D.KID,D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH,D.EXPENDITURE_SUBJECT_CODE,CURRENT_AMT2 ,SUM_AMT2 , SUM(CASE WHEN LENGTH(D.EXPENDITURE_SUBJECT_CODE) = 3 THEN D.CURRENT_AMT2 ELSE 0 END) OVER(PARTITION BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH) CURRENT_AMT, SUM(CASE WHEN LENGTH(D.EXPENDITURE_SUBJECT_CODE) = 3 THEN D.SUM_AMT2 ELSE 0 END) OVER(PARTITION BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH) SUM_AMT FROM NHTC_INCOME_AND_EXPENDITURE D WHERE D.DATA_FLAG = 'M' AND TRIM(D.EXPENDITURE_SUBJECT_CODE) IS NOT NULL )D WHERE D.EXPENDITURE_SUBJECT_CODE ='9991' AND (D.CURRENT_AMT2 <> D.CURRENT_AMT OR D.SUM_AMT2 <> D.SUM_AMT)

执行结果：

2）增加索引再看:length函数 4.7s

因为条件：LENGTH(D.INCOME_SUBJECT_CODE) = 3 无法避免，又不想走全表扫描，针对length函数优化，增加索引：

create index IDX_NHTC_CODE1_LEN on NHTC_INCOME_AND_EXPENDITURE (LENGTH(INCOME_SUBJECT_CODE)); create index IDX_NHTC_CODE2_LEN on NHTC_INCOME_AND_EXPENDITURE (LENGTH(EXPENDITURE_SUBJECT_CODE));

实验结果：

3）优化语句3 ：修改条件-不走全表扫描 4.7s
SELECT TO_CHAR(SYSDATE,'YYYY-MM'),SYSDATE,'NHTC_INCOME_AND_EXPENDITURE', -- '-','COUNT_DIFF','','cc', D.KID || ',' || D.SOCIAL_CREDIT_CODE || ',' || D.YEAR_MONTH || ',本月：' || D.CURRENT_AMT2 || ' 与 '|| D.CURRENT_AMT || ',本年：' || D.SUM_AMT2|| ' 与 '||D.SUM_AMT , D.SOCIAL_CREDIT_CODE,'本月数、本年数与支出合计的不符(支出)','1',D.YEAR_MONTH, DECODE(SUBSTR(D.SOCIAL_CREDIT_CODE,1,3),'014',D.SOCIAL_CREDIT_CODE,''),GET_SEQBATCH FROM ( SELECT D.KID,D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH,D.EXPENDITURE_SUBJECT_CODE,CURRENT_AMT2 ,SUM_AMT2 , SUM(CASE WHEN LENGTH(D.EXPENDITURE_SUBJECT_CODE) = 3 THEN D.CURRENT_AMT2 ELSE 0 END) OVER(PARTITION BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH) CURRENT_AMT, SUM(CASE WHEN LENGTH(D.EXPENDITURE_SUBJECT_CODE) = 3 THEN D.SUM_AMT2 ELSE 0 END) OVER(PARTITION BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH) SUM_AMT FROM NHTC_INCOME_AND_EXPENDITURE D WHERE D.DATA_FLAG = 'M' AND /*trim(EXPENDITURE_SUBJECT_CODE) is not null*/ LENGTH(EXPENDITURE_SUBJECT_CODE) >=3 )D WHERE D.EXPENDITURE_SUBJECT_CODE ='9991' AND (D.CURRENT_AMT2 <> D.CURRENT_AMT OR D.SUM_AMT2 <> D.SUM_AMT);

执行结果：

4）优化语句4：根据业务缩小范围 1.4s
SELECT TO_CHAR(SYSDATE,'YYYY-MM'),SYSDATE,'NHTC_INCOME_AND_EXPENDITURE', -- '-','COUNT_DIFF','','cc', D.KID || ',' || D.SOCIAL_CREDIT_CODE || ',' || D.YEAR_MONTH || ',本月：' || D.CURRENT_AMT2 || ' 与 '|| D.CURRENT_AMT || ',本年：' || D.SUM_AMT2|| ' 与 '||D.SUM_AMT , D.SOCIAL_CREDIT_CODE,'本月数、本年数与支出合计的不符(支出)','1',D.YEAR_MONTH, DECODE(SUBSTR(D.SOCIAL_CREDIT_CODE,1,3),'014',D.SOCIAL_CREDIT_CODE,''),GET_SEQBATCH FROM ( SELECT D.KID,D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH,D.EXPENDITURE_SUBJECT_CODE,CURRENT_AMT2 ,SUM_AMT2 , SUM(CASE WHEN LENGTH(D.EXPENDITURE_SUBJECT_CODE) = 3 THEN D.CURRENT_AMT2 ELSE 0 END) OVER(PARTITION BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH) CURRENT_AMT, SUM(CASE WHEN LENGTH(D.EXPENDITURE_SUBJECT_CODE) = 3 THEN D.SUM_AMT2 ELSE 0 END) OVER(PARTITION BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH) SUM_AMT FROM NHTC_INCOME_AND_EXPENDITURE D WHERE D.DATA_FLAG = 'M' AND LENGTH(EXPENDITURE_SUBJECT_CODE) >=3 and LENGTH(EXPENDITURE_SUBJECT_CODE) <=4 )D WHERE D.EXPENDITURE_SUBJECT_CODE ='9991' AND (D.CURRENT_AMT2 <> D.CURRENT_AMT OR D.SUM_AMT2 <> D.SUM_AMT);

执行结果：

5) 其他索引：1s
CREATE INDEX IDX_NHTC_INCOME_AND_EXPENDITURE_QUERY1 ON NHTC_INCOME_AND_EXPENDITURE ( DATA_FLAG, INCOME_SUBJECT_CODE, SOCIAL_CREDIT_CODE, YEAR_MONTH ); CREATE INDEX IDX_NHTC_INCOME_AND_EXPENDITURE_QUERY2 ON NHTC_INCOME_AND_EXPENDITURE ( DATA_FLAG, EXPENDITURE_SUBJECT_CODE, SOCIAL_CREDIT_CODE, YEAR_MONTH );

执行结果：

6）测试group by 后的连接
SELECT TO_CHAR(SYSDATE,'YYYY-MM'),SYSDATE,'NHTC_INCOME_AND_EXPENDITURE', '-','COUNT_DIFF','','', D.KID || ',' || D.SOCIAL_CREDIT_CODE || ',' || D.YEAR_MONTH || ',本月：' || A.CURRENT_AMT2 || ' 与 '|| D.CURRENT_AMT2 || ',本年：' || A.SUM_AMT2|| ' 与 '||d.SUM_AMT2 , D.SOCIAL_CREDIT_CODE,'本月数、本年数与支出合计的不符(支出)','1',D.YEAR_MONTH, DECODE(SUBSTR(D.SOCIAL_CREDIT_CODE,1,3),'014',D.SOCIAL_CREDIT_CODE,''),get_seqbatch FROM (SELECT D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH, SUM(D.CURRENT_AMT2) CURRENT_AMT2 , SUM(D.SUM_AMT2) SUM_AMT2 FROM NHTC_INCOME_AND_EXPENDITURE D WHERE D.DATA_FLAG = 'M' AND LENGTH(D.EXPENDITURE_SUBJECT_CODE) = 3 GROUP BY D.SOCIAL_CREDIT_CODE,D.YEAR_MONTH ) A,NHTC_INCOME_AND_EXPENDITURE D WHERE D.EXPENDITURE_SUBJECT_CODE = '9991' AND DATA_FLAG = 'M' AND A.SOCIAL_CREDIT_CODE = D.SOCIAL_CREDIT_CODE AND A.YEAR_MONTH = D.YEAR_MONTH AND (A.CURRENT_AMT2 <> D.CURRENT_AMT2 OR A.SUM_AMT2 <> D.SUM_AMT2);

执行计划对比：

执行结果：很久都出不来，毕竟大表连接2次

3.查看执行计划

EXPLAIN PLAN FOR <上述优化后的SQL>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

关注是否有INDEX RANGE SCAN或INDEX UNIQUE SCAN，避免TABLE ACCESS FULL。

1）trim() 函数时，发现是全表扫描

2）增加lengh() 函数索引时，发现 走了 INDEX RANGE SCAN

4.总结

1）大批量数据不用group by, 毕竟还得两张表连接 (6s <-- 28s)

2) 避免全表扫描 ( trim就会全表扫描，4.7s <-- 1.2s )

3）表数据范围尽量缩小 ( 5s <--1.2s)

4）索引的作用 ( length索引，1.4s <-- 1.2s , 6s <-- 4.7s)

实验验证：ok

项目管理--相关知识

项目管理-项目绩效域1/2-CSDN博客

项目管理-项目绩效域1/2_八大绩效域和十大管理有什么联系-CSDN博客

项目管理-项目绩效域2/2_绩效域 团不策划-CSDN博客

高项-案例分析万能答案（作业分享）-CSDN博客

项目管理-计算题公式【复习】_项目管理进度计算题公式:乐观-CSDN博客

项目管理-配置管理与变更-CSDN博客

项目管理-项目管理科学基础-CSDN博客

项目管理-高级项目管理-CSDN博客

项目管理-相关知识（组织通用治理、组织通用管理、法律法规与标准规范）-CSDN博客

Oracle其他文档，希望互相学习，共同进步

Oracle-找回误删的表数据(LogMiner 挖掘日志)_oracle日志挖掘恢复数据-CSDN博客

oracle 跟踪文件--审计日志_oracle审计日志-CSDN博客

ORA-12899报错，遇到数据表某字段长度奇怪现象：“Oracle字符型，长度50”但length查却没有50_varchar(50) oracle 超出截断-CSDN博客

EXP-00091: Exporting questionable statistics.解决方案-CSDN博客

Oracle 更换监听端口-CSDN博客

编程学习 学习日记 实战经验
← 返回列表