Fgwzd项目sql语句优化
原sql如下：
–页脚统计
select /*+ ADAPTIVE_NPLN_FLAG(3) */
count(0) ,
nvl(sum(TOTAL_INVEST), 0),
nvl(sum(NUM), 0) ,
nvl(sum(INVEST_REQUIREMENTS), 0)
FROM
(
SELECT
LBFN.ID ,
LBFN.FILE_NO ,
LBFN.FILE_NAME ,
COUNT(LBPM.ID) AS NUM ,
SUM(NVL(LBPM.INVESTMENT_TOTAL, 0)) AS TOTAL_INVEST ,
LBFN.LOCKED ,
LBFN.STATUS ,
TO_CHAR(LBFN.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’) AS SUBMIT_TIME ,
LBFN.FILE_VERSION ,
LBFN.DEPARTMENT_GUID ,
SUM(case when LBPM.LOCAL_BOND =1 and to_char(LBPM.EXPECT_ENDYEAR, ‘yyyy-MM’) >= to_char(add_months(SYSDATE, 2), ‘yyyy-MM’) then 0 when LBPM.ID is null then 0 else CASE WHEN INSTR(SS.PARAM_VALUE, LBFN.FILE_NAME)>0 then 0 else 1 end end) AS BOND_CHECK,
SUM(case when regexp_like(LBPM.PRO_CODE_AREA, ‘[-]{4}-[-]{6}-[-]{2}-[-]{2}-[-]{6}$’)=1 or LBPM.ID IS NULL then 0 else 1 end) AS CODE_CHECK,
SUM(case when LBPM.LOCAL_BOND =1 and (LBPM.PROJECT_INCOME_SOURCE IS NULL OR LBPM.PROJECT_INCOME_SOURCE=’’
) then 1 else 0 end) AS SOURCE_CHECK ,
LBFN.YEAR ,
LBFN.SOURCE ,
LBFN.SYS_LOCK ,
LBFN.WRAPUP ,
sum(case when LBFN.year=2015 then nvl(LBPIEI.APPLY_CAPTIAL_2015, 0) when LBFN.year=2016 then nvl(LBPIEI.APPLY_CAPTIAL_2016, 0) when LBFN.year=2017 then nvl(LBPIEI.APPLY_CAPTIAL_2017, 0) when LBFN.year=2018 then nvl(LBPIEI.APPLY_CAPTIAL_2018, 0) when LBFN.year=2019 then nvl(LBPIEI.APPLY_CAPTIAL_2019, 0) when LBFN.year=2020 then nvl(LBPIEI.APPLY_CAPTIAL_2020, 0) when LBFN.year=2021 then nvl(LBPIEI.APPLY_CAPTIAL_2021, 0) when LBFN.year=2022 then nvl(LBPIEI.APPLY_CAPTIAL_2022, 0) when LBFN.year=2023 then nvl(LBPIEI.APPLY_CAPTIAL_2023, 0) when LBFN.year=2024 then nvl(LBPIEI.APPLY_CAPTIAL_2024, 0) when LBFN.year=2025 then nvl(LBPIEI.APPLY_CAPTIAL_2025, 0) when LBFN.year=2026 then nvl(LBPIEI.APPLY_CAPTIAL_2026, 0) when LBFN.year=2027 then nvl(LBPIEI.APPLY_CAPTIAL_2027, 0) when LBFN.year=2028 then nvl(LBPIEI.APPLY_CAPTIAL_2028, 0) when LBFN.year=2029 then nvl(LBPIEI.APPLY_CAPTIAL_2029, 0) when LBFN.year=2030 then nvl(LBPIEI.APPLY_CAPTIAL_2030, 0) end) as INVEST_REQUIREMENTS,
REPLACE(LBFN.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) AS DEPARTMENT_FULLNAME
FROM
table1 LBFN
LEFT JOIN
(
SELECT
LBP.ID ,
LBP.FILE_NO_ID ,
LBPIEM.LOCAL_BOND ,
LBPIEM.EXPECT_ENDYEAR ,
LBPIEM.PRO_CODE_AREA ,
LBPIEM.PROJECT_INCOME_SOURCE,
NVL(LBPIEM.INVESTMENT_TOTAL, 0) AS INVESTMENT_TOTAL
FROM
table2 LBP
JOIN table2_INFO_EXT_MASTER LBPIEM
ON
LBP.ID = LBPIEM.PROJECT_ID
WHERE
1=1
)
LBPM ON LBFN.ID = LBPM.FILE_NO_ID
LEFT JOIN
(
select
APPLY_CAPTIAL_2015,
APPLY_CAPTIAL_2016,
APPLY_CAPTIAL_2017,
APPLY_CAPTIAL_2018,
APPLY_CAPTIAL_2019,
APPLY_CAPTIAL_2020,
APPLY_CAPTIAL_2021,
APPLY_CAPTIAL_2022,
APPLY_CAPTIAL_2023,
APPLY_CAPTIAL_2024,
APPLY_CAPTIAL_2025,
PROJECT_ID ,
APPLY_CAPTIAL_2026,
APPLY_CAPTIAL_2027,
APPLY_CAPTIAL_2028,
APPLY_CAPTIAL_2029,
APPLY_CAPTIAL_2030
from
table2_INFO_EXT_INVEST
where
project_item_ext_id=‘A00069’
)
LBPIEI
ON
LBPM.ID = LBPIEI.PROJECT_ID
LEFT JOIN SYSTEM_SETTINGS SS
ON
SS.PARAM_NAME = ‘SYSTEM_LOCALBOND_ENDTIME_FLAG’
–WHERE
–LBFN.DEPARTMENT_GUID = ‘JIANGXI-1’
GROUP BY
LBFN.ID ,
LBFN.FILE_NO ,
LBFN.FILE_NAME ,
LBFN.LOCKED ,
LBFN.STATUS ,
TO_CHAR(LBFN.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’),
LBFN.FILE_VERSION ,
LBFN.DEPARTMENT_GUID ,
LBFN.YEAR ,
LBFN.SOURCE ,
LBFN.SYS_LOCK ,
REPLACE(LBFN.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) ,
LBFN.WRAPUP
);
其中加粗部分，表示与ss表进行笛卡尔积关联。
经过沟通，需要进行笛卡尔积关联的表是一个一对一配置的码表：
select ss.param_value from table4 SS where ss.PARAM_NAME = ‘SYSTEM_table2_ENDTIME_FLAG’;
优化方法如下：
消除对ss表的笛卡尔积关联，把语句拆开成两步用高级语言做，1、执行select ss.param_value from SYSTEM_SETTINGS SS where ss.PARAM_NAME = ‘SYSTEM_table2_ENDTIME_FLAG’; 2、把这个param_value作为常量传到sql的select字句里的加粗部分：
–页脚统计
select /*+ ADAPTIVE_NPLN_FLAG(3) /
count(0) ,
nvl(sum(TOTAL_INVEST), 0),
nvl(sum(NUM), 0) ,
nvl(sum(INVEST_REQUIREMENTS), 0)
FROM
(
SELECT
LBFN.ID ,
LBFN.FILE_NO ,
LBFN.FILE_NAME ,
COUNT(LBPM.ID) AS NUM ,
SUM(NVL(LBPM.INVESTMENT_TOTAL, 0)) AS TOTAL_INVEST ,
LBFN.LOCKED ,
LBFN.STATUS ,
TO_CHAR(LBFN.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’) AS SUBMIT_TIME ,
LBFN.FILE_VERSION ,
LBFN.DEPARTMENT_GUID ,
SUM(case when LBPM.LOCAL_BOND =1 and to_char(LBPM.EXPECT_ENDYEAR, ‘yyyy-MM’) >= to_char(add_months(SYSDATE, 2), ‘yyyy-MM’) then 0 when LBPM.ID is null then 0 else CASE WHEN INSTR(/SS.PARAM_VALUE/’PARAM_VALUE’, LBFN.FILE_NAME)>0 then 0 else 1 end end) AS BOND_CHECK,
SUM(case when regexp_like(LBPM.PRO_CODE_AREA, ‘[-]{4}-[-]{6}-[-]{2}-[-]{2}-[-]{6}$’)=1 or LBPM.ID IS NULL then 0 else 1 end) AS CODE_CHECK,
SUM(case when LBPM.LOCAL_BOND =1 and (LBPM.PROJECT_INCOME_SOURCE IS NULL OR LBPM.PROJECT_INCOME_SOURCE=’’
) then 1 else 0 end) AS SOURCE_CHECK ,
LBFN.YEAR ,
LBFN.SOURCE ,
LBFN.SYS_LOCK ,
LBFN.WRAPUP ,
sum(case when LBFN.year=2015 then nvl(LBPIEI.APPLY_CAPTIAL_2015, 0) when LBFN.year=2016 then nvl(LBPIEI.APPLY_CAPTIAL_2016, 0) when LBFN.year=2017 then nvl(LBPIEI.APPLY_CAPTIAL_2017, 0) when LBFN.year=2018 then nvl(LBPIEI.APPLY_CAPTIAL_2018, 0) when LBFN.year=2019 then nvl(LBPIEI.APPLY_CAPTIAL_2019, 0) when LBFN.year=2020 then nvl(LBPIEI.APPLY_CAPTIAL_2020, 0) when LBFN.year=2021 then nvl(LBPIEI.APPLY_CAPTIAL_2021, 0) when LBFN.year=2022 then nvl(LBPIEI.APPLY_CAPTIAL_2022, 0) when LBFN.year=2023 then nvl(LBPIEI.APPLY_CAPTIAL_2023, 0) when LBFN.year=2024 then nvl(LBPIEI.APPLY_CAPTIAL_2024, 0) when LBFN.year=2025 then nvl(LBPIEI.APPLY_CAPTIAL_2025, 0) when LBFN.year=2026 then nvl(LBPIEI.APPLY_CAPTIAL_2026, 0) when LBFN.year=2027 then nvl(LBPIEI.APPLY_CAPTIAL_2027, 0) when LBFN.year=2028 then nvl(LBPIEI.APPLY_CAPTIAL_2028, 0) when LBFN.year=2029 then nvl(LBPIEI.APPLY_CAPTIAL_2029, 0) when LBFN.year=2030 then nvl(LBPIEI.APPLY_CAPTIAL_2030, 0) end) as INVEST_REQUIREMENTS,
REPLACE(LBFN.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) AS DEPARTMENT_FULLNAME
FROM
table1 LBFN
LEFT JOIN
(
SELECT
LBP.ID ,
LBP.FILE_NO_ID ,
LBPIEM.LOCAL_BOND ,
LBPIEM.EXPECT_ENDYEAR ,
LBPIEM.PRO_CODE_AREA ,
LBPIEM.PROJECT_INCOME_SOURCE,
NVL(LBPIEM.INVESTMENT_TOTAL, 0) AS INVESTMENT_TOTAL
FROM
table2 LBP
JOIN table2_INFO_EXT_MASTER LBPIEM
ON
LBP.ID = LBPIEM.PROJECT_ID
WHERE
1=1
)
LBPM ON LBFN.ID = LBPM.FILE_NO_ID
LEFT JOIN
(
select
APPLY_CAPTIAL_2015,
APPLY_CAPTIAL_2016,
APPLY_CAPTIAL_2017,
APPLY_CAPTIAL_2018,
APPLY_CAPTIAL_2019,
APPLY_CAPTIAL_2020,
APPLY_CAPTIAL_2021,
APPLY_CAPTIAL_2022,
APPLY_CAPTIAL_2023,
APPLY_CAPTIAL_2024,
APPLY_CAPTIAL_2025,
PROJECT_ID ,
APPLY_CAPTIAL_2026,
APPLY_CAPTIAL_2027,
APPLY_CAPTIAL_2028,
APPLY_CAPTIAL_2029,
APPLY_CAPTIAL_2030
from
table2_INFO_EXT_INVEST
where
project_item_ext_id=‘A00069’
)
LBPIEI
ON
LBPM.ID = LBPIEI.PROJECT_ID
/ LEFT JOIN SYSTEM_SETTINGS SS
ON
SS.PARAM_NAME = ‘SYSTEM_LOCALBOND_ENDTIME_FLAG’ */
–WHERE
–LBFN.DEPARTMENT_GUID = ‘JIANGXI-1’
GROUP BY
LBFN.ID ,
LBFN.FILE_NO ,
LBFN.FILE_NAME ,
LBFN.LOCKED ,
LBFN.STATUS ,
TO_CHAR(LBFN.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’),
LBFN.FILE_VERSION ,
LBFN.DEPARTMENT_GUID ,
LBFN.YEAR ,
LBFN.SOURCE ,
LBFN.SYS_LOCK ,
REPLACE(LBFN.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) ,
LBFN.WRAPUP
);
优化后，执行时间从6秒，下降到2秒左右。
【sql|达梦sql优化实践5】达梦云适配技术社区的网址。https://eco.dameng.com

推荐阅读

• 

  吐奶|宝宝吐奶怎么办？先别急着送医院，看看这些干货
• 

  cookie底层分析,广告cookie 第三方分析
• 

  新居落成祝福语 盘点新居落成祝福语
• 

  如何登录云端服务器？ 云端服务器怎么登陆
• 

  桔子可以放冰箱保鲜吗？如何正确保存桔子以延长其新鲜度
• 

  涂防晒霜需要卸妆吗 不化妆只涂防晒霜需要卸妆吗
• 

  网盘解析,rar网盘解析
• 

  第一次使用充电宝要全部用尽吗 首次使用充电宝要全部用尽吗
• 

  用于捐赠物品的进项税可以抵扣吗 哪些捐赠允许税前抵扣，税法业务招待费扣除标准
• 

  表格中分析出数据的奇偶,excel表格怎么分析
• 

  大麦若叶青汁过期还能喝吗
• 

  家庭常用锅炉的清洗方法，家用燃气锅炉清洗
• 

  中国国旗简笔画 中国国旗简笔画怎么画
• 

  范成大简介及作品 范成大的作品有哪些
• 

  英文辞职报告模板
• 

  博世壁挂炉报ce怎么处理 郑州市博世壁挂炉总经销处理方法
• 

  安卓手机重装系统软件,手机出现病例怎么办?教你如何自行刷机
• 

  山楂树栽种注意事项 山楂树种植注意事项
• 

  手机投影仪可以给手机充电吗？荣耀手机怎么连接投影仪？
• 

  ug自动判断创建基准面的方法

• sql|达梦数据库sql优化
• sql|达梦sql优化实践6
• 达梦数据库|达梦SQL调优
• DM8|达梦（DM8）SQL优化技巧
• sql|达梦sql优化实践7
• mysql|面了个腾讯出来的00后，我见识到了什么叫“精通MySQL调优”
• 数据库|MySQL性能优化的21个最佳实践（转载）
• 后端|SpringBoot整合Redis集群
• 酷开科技 × StarRocks（统一 OLAP 分析引擎，全面打造数字化的 OTT 模式）