sql - 如何优化这个复杂的查询？
问题描述
我正在运行具有很多条件的复杂查询。在大型数据库上，查询需要 2 分钟以上。我正在为我的项目使用 Django。我可以优化这个查询吗？或者我应该使用 Django ORM 查询？哪个更快？LN_ACCOUNT 表的行数超过 300 万行。任何建议和帮助将不胜感激。太感谢了。
SELECT COUNT(*), SUM((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = L.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT in ('1','4','5','8')
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '20.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (
select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1
) = 'Y'
)))/-100
FROM LN_CARD L
and
L.LOAN_ID IN (
select distinct (LOAN_ID) LID
from (
select (
select LOAN_ID
from LN_ACCOUNT s
where s.ACCOUNT_CODE = ac.CODE
and s.DATE_NEXT > '21.11.2020'
and s.DATE_VALIDATE <= '21.11.2020'
and rownum = 1) as LOAN_ID
from ACCOUNTS ac,
V_REP_LN_BAL vr
where vr.BAL_ACC = ac.CODE_COA
and vr.TYPE_BAL in (1, 2, 3, 4)
) lnIDS
where LOAN_ID is not null)
and (
select sum(abs(acc.SALDO_EQUIVAL_OUT))
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT in ('1','4','5','8')
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (
select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1
) = 'Y'
)
)
+ abs(
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 3
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0) +
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 7
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0) +
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 79
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0)
)
+ abs(NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 46
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0))
+ abs(
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 6
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0) +
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 52
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0) +
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 53
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0) +
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 54
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and (substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y' from
V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1) = 'Y'
)),0)
) <> 0;
解决方案
您正在执行多个嵌套子查询：
+ abs(
NVL((select sum(acc.SALDO_EQUIVAL_OUT)
from LN_ACCOUNT lna, ACCOUNTS acc
where lna.LOAN_ID = l.LOAN_ID
and lna.LOAN_TYPE_ACCOUNT = 3
and lna.DATE_NEXT > '21.11.2020'
and lna.DATE_VALIDATE <= '21.11.2020'
and acc.CODE = lna.ACCOUNT_CODE
and ( substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
or (select 'Y'
from V_REP_LN_BAL v
where v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
and ROWNUM = 1
) = 'Y'
)
),
0
)
唯一的区别似乎是lna.LOAN_TYPE_ACCOUNT您正在过滤。连接所有这些，以便您只需要查询一次表：
+ COALESCE(
(
SELECT SUM( t.abs_total )
FROM (
SELECT lna.LOAN_ID,
lna.LOAN_TYPE_ACCOUNT,
ABS( SUM( acc.SALDO_EQUIVAL_OUT ) ) AS abs_total
FROM LN_ACCOUNT lna
INNER JOIN ACCOUNTS acc
ON ( acc.CODE = lna.ACCOUNT_CODE )
WHERE lna.LOAN_TYPE_ACCOUNT IN ( 3, 7, 79, 46, 6, 52, 53, 54 )
AND lna.DATE_NEXT > DATE '2020-11-21'
AND lna.DATE_VALIDATE <= DATE '2020-11-21'
AND ( substr(lna.ACCOUNT_CODE, -20, 3) in ('163','915')
OR EXISTS( SELECT 1
FROM V_REP_LN_BAL v
WHERE v.BAL_ACC like substr(lna.ACCOUNT_CODE, -20, 3)||'%'
)
)
GROUP BY
lna.LOAN_ID,
lna.LOAN_ACCOUNT_TYPE
) t
WHERE t.LOAN_ID = l.LOAN_ID
),
0
)
LOAN_TYPE_ACCOUNT在使用之前，您需要检查以确保您仍然获得与似乎聚合多个相同的结果ABS；但是，如果是这种情况，那么LOAN_TYPE_ACCOUNT您SELECT可以GROUP BY使用：
CASE
WHEN LOAN_TYPE_ACCOUNT IN ( 3, 7, 79 ) THEN 'Group1'
WHEN LOAN_TYPE_ACCOUNT IN ( 46 ) THEN 'Group2'
WHEN LOAN_TYPE_ACCOUNT IN ( 6, 52, 53, 54 ) THEN 'Group3'
END
推荐阅读
- c# - Autofac试图加载不相关的类
- python - 在数据流中找到迄今为止的前 K 个最小值
- wpf - 如何让整个团队在 Visual Studio 中拥有完全相同的设置，以便每个开发人员都有相同的先决条件和设置来工作？
- javascript - 更新消息角色 discord.js
- python - 如何测试在某些情况下必须无限循环的函数？
- firebase - Firebase 帐户验证电子邮件无法在 Android 上使用动态链接 URL 发送
- c++ - 为什么将类对象作为参数的外部函数在返回相同的对象时会调用移动构造函数
- javascript - 获取可能的问题答案路径 (JavaScript)
- tensorflow - 如何在不干扰 TF>2.0 Model Graph 的情况下实现带有张量评估的自定义 Keras 序数损失函数？
- reactjs - 更新的打字稿 - 在组合减速器功能 TS2344 上接收错误