2021-11-01

Fgwzd项目列表项目查询 sql语句优化
原sql如下：

SELECT
table1.ID ,
table1.FILE_NO ,
table1.FILE_NAME ,
COUNT(LBPM.ID) AS NUM ,
SUM(NVL(LBPM.INVESTMENT_TOTAL, 0)) AS TOTAL_INVEST ,
table1.LOCKED ,
table1.STATUS ,
TO_CHAR(table1.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’) AS SUBMIT_TIME ,
table1.FILE_VERSION ,
table1.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, table1.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 ,
table1.YEAR ,
table1.SOURCE ,
table1.SYS_LOCK ,
table1.WRAPUP ,
sum(case when table1.year=2015 then nvl(LBPIEI.col2_2015, 0) when table1.year=2016 then nvl(LBPIEI.col2_2016, 0) when table1.year=2017 then nvl(LBPIEI.col2_2017, 0) when table1.year=2018 then nvl(LBPIEI.col2_2018, 0) when table1.year=2019 then nvl(LBPIEI.col2_2019, 0) when table1.year=2020 then nvl(LBPIEI.col2_2020, 0) when table1.year=2021 then nvl(LBPIEI.col2_2021, 0) when table1.year=2022 then nvl(LBPIEI.col2_2022, 0) when table1.year=2023 then nvl(LBPIEI.col2_2023, 0) when table1.year=2024 then nvl(LBPIEI.col2_2024, 0) when table1.year=2025 then nvl(LBPIEI.col2_2025, 0) when table1.year=2026 then nvl(LBPIEI.col2_2026, 0) when table1.year=2027 then nvl(LBPIEI.col2_2027, 0) when table1.year=2028 then nvl(LBPIEI.col2_2028, 0) when table1.year=2029 then nvl(LBPIEI.col2_2029, 0) when table1.year=2030 then nvl(LBPIEI.col2_2030, 0) end) as INVEST_REQUIREMENTS,
REPLACE(table1.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) AS DEPARTMENT_FULLNAME
FROM
table1 table1
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 table1.ID = LBPM.FILE_NO_ID
LEFT JOIN
(
select
col2_2015,
col2_2016,
col2_2017,
col2_2018,
col2_2019,
col2_2020,
col2_2021,
col2_2022,
col2_2023,
col2_2024,
col2_2025,
PROJECT_ID ,
col2_2026,
col2_2027,
col2_2028,
col2_2029,
col2_2030
from
table2_INFO_EXT_INVEST
where
project_item_ext_id=‘A00069’
)
LBPIEI
ON
LBPM.ID = LBPIEI.PROJECT_ID
LEFT JOIN table4 SS
ON
SS.PARAM_NAME = ‘SYSTEM_col1_ENDTIME_FLAG’
–WHERE
–table1.DEPARTMENT_GUID = ‘JIANGXI-1’
GROUP BY
table1.ID ,
table1.FILE_NO ,
table1.FILE_NAME ,
table1.LOCKED ,
table1.STATUS ,
TO_CHAR(table1.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’),
table1.FILE_VERSION ,
table1.DEPARTMENT_GUID ,
table1.YEAR ,
table1.SOURCE ,
table1.SYS_LOCK ,
REPLACE(table1.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) ,
table1.WRAPUP
ORDER BY
table1.STATUS ASC,
table1.FILE_VERSION DESC ;
其中加粗部分，表示与ss表进行笛卡尔积关联。
经过沟通，需要进行笛卡尔积关联的表是一个一对一配置的码表：
select ss.param_value from table4 SS where ss.PARAM_NAME = ‘SYSTEM_table2_ENDTIME_FLAG’;
优化方法如下：
消除对ss表的笛卡尔积关联，把语句拆开成两步用高级语言做，1、执行select ss.param_value from table4 SS where ss.PARAM_NAME = ‘SYSTEM_table2_ENDTIME_FLAG’; 2、把这个param_value作为常量传到sql的select字句里的加粗部分：

SELECT
table1.ID ,
table1.FILE_NO ,
table1.FILE_NAME ,
COUNT(LBPM.ID) AS NUM ,
SUM(NVL(LBPM.INVESTMENT_TOTAL, 0)) AS TOTAL_INVEST ,
table1.LOCKED ,
table1.STATUS ,
TO_CHAR(table1.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’) AS SUBMIT_TIME ,
table1.FILE_VERSION ,
table1.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’, table1.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 ,
table1.YEAR ,
table1.SOURCE ,
table1.SYS_LOCK ,
table1.WRAPUP ,
sum(case when table1.year=2015 then nvl(LBPIEI.col2_2015, 0) when table1.year=2016 then nvl(LBPIEI.col2_2016, 0) when table1.year=2017 then nvl(LBPIEI.col2_2017, 0) when table1.year=2018 then nvl(LBPIEI.col2_2018, 0) when table1.year=2019 then nvl(LBPIEI.col2_2019, 0) when table1.year=2020 then nvl(LBPIEI.col2_2020, 0) when table1.year=2021 then nvl(LBPIEI.col2_2021, 0) when table1.year=2022 then nvl(LBPIEI.col2_2022, 0) when table1.year=2023 then nvl(LBPIEI.col2_2023, 0) when table1.year=2024 then nvl(LBPIEI.col2_2024, 0) when table1.year=2025 then nvl(LBPIEI.col2_2025, 0) when table1.year=2026 then nvl(LBPIEI.col2_2026, 0) when table1.year=2027 then nvl(LBPIEI.col2_2027, 0) when table1.year=2028 then nvl(LBPIEI.col2_2028, 0) when table1.year=2029 then nvl(LBPIEI.col2_2029, 0) when table1.year=2030 then nvl(LBPIEI.col2_2030, 0) end) as INVEST_REQUIREMENTS,
REPLACE(table1.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) AS DEPARTMENT_FULLNAME
FROM
table1 table1
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 table1.ID = LBPM.FILE_NO_ID
LEFT JOIN
(
select
col2_2015,
col2_2016,
col2_2017,
col2_2018,
col2_2019,
col2_2020,
col2_2021,
col2_2022,
col2_2023,
col2_2024,
col2_2025,
PROJECT_ID ,
col2_2026,
col2_2027,
col2_2028,
col2_2029,
col2_2030
from
table2_INFO_EXT_INVEST
where
project_item_ext_id=‘A00069’
)
LBPIEI
ON
LBPM.ID = LBPIEI.PROJECT_ID
/LEFT JOIN table4 SS
ON
SS.PARAM_NAME = ‘SYSTEM_col1_ENDTIME_FLAG’/
–WHERE
–table1.DEPARTMENT_GUID = ‘JIANGXI-1’
GROUP BY
table1.ID ,
table1.FILE_NO ,
table1.FILE_NAME ,
table1.LOCKED ,
table1.STATUS ,
TO_CHAR(table1.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’),
table1.FILE_VERSION ,
table1.DEPARTMENT_GUID ,
table1.YEAR ,
table1.SOURCE ,
table1.SYS_LOCK ,
REPLACE(table1.DEPARTMENT_FULLNAME, ‘varchar1’, ‘’) ,
table1.WRAPUP
ORDER BY
table1.STATUS ASC,
table1.FILE_VERSION DESC ;
优化后，执行时间从6秒，下降到2秒左右。

达梦云适配技术社区的网址。https://eco.dameng.com