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聚合计算量大。‌优化方案‌‌利用窗口函数Window Functions‌使用SUM() OVER()替代子查询聚合避免多次扫描表或复杂的 Hash Join。‌确保连接字段有索引‌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优化语句16sSELECT 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.7sSELECT 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.4sSELECT 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) 其他索引1sCREATE 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。1trim() 函数时发现是全表扫描2增加lengh() 函数索引时发现 走了 INDEX RANGE SCAN4.总结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博客