sql里面调用函数的性能问题

newkid · 发表于 2017-10-19 23:11

oracle_cj 发表于 2017-10-19 08:15
函数的逻辑，不太容易合并，我的第一想法就是合并掉函数，无奈逻辑比较复杂，里面涉及7，8张表

你这函数被调用了40W次。
如果整个函数拿出来太复杂，看看有没有可能把一些判断的捷径拿出来。比方说，有没有简单一点的逻辑可以快速判断出那两个状态？

oracle_cj · 发表于 2017-10-20 09:47

newkid 发表于 2017-10-19 23:11
你这函数被调用了40W次。
如果整个函数拿出来太复杂，看看有没有可能把一些判断的捷径拿出来。比方说， ...

问题已经解决了，多谢建议。
昨天通过将状态过滤带到后面的处理逻辑的思路行不通，后面有一条sql 用到了grouping的语句，我就加了一个字段到原有的语句中，居然跑不出来，不加还能跑出来，有点不解。

SELECT T.VENDOR_ID,
T.INVOICE_ID,
STATUS_FLAG, --审批字段
GROUPING_ID,
VENDOR_TYPE_DISP,
T.VENDOR_NAME,
T.VENDOR_SITE_CODE,
T.INVOICE_TYPE,
T.INVOICE_NUM,
DUE_DATE,
T.GROSS_AMOUNT,
T.CURR_CODE,
INVOICE_DATE,
T.ACC_SEG3,
T.DESCRIPTION,
T.ATTRIBUTE3,
T.USER_NAME,
T.ATTRIBUTE2,
T.ATTRIBUTE4,
T.ATTRIBUTE5,
TRIM(REPLACE(T.TOTAL_AMOUNT, ',', '')),
TRIM(REPLACE(T.TOTAL_AGING_AMOUNT, ',', ''))
from (select T.VENDOR_ID,
T.INVOICE_ID,
STATUS_FLAG, --审批字段
GROUPING_ID(T.VENDOR_ID,
STATUS_FLAG, --审批字段
T.VENDOR_TYPE_DISP,
T.VENDOR_NAME,
T.VENDOR_SITE_CODE,
T.INVOICE_TYPE,
T.INVOICE_NUM,
T.INVOICE_ID,
T.DUE_DATE,
T.ACC_SEG3) GROUPING_ID,
DECODE(GROUPING_ID(T.VENDOR_ID,
STATUS_FLAG, --审批字段
T.VENDOR_TYPE_DISP,
T.VENDOR_NAME,
T.VENDOR_SITE_CODE,
T.INVOICE_TYPE,
T.INVOICE_NUM,
T.INVOICE_ID,
T.DUE_DATE,
T.ACC_SEG3),
207,
'小计',
223,
'小计',
255,
'合计',
T.VENDOR_TYPE_DISP) VENDOR_TYPE_DISP,
T.VENDOR_NAME,
T.VENDOR_SITE_CODE,
T.INVOICE_TYPE,
T.INVOICE_NUM,
TO_CHAR(T.DUE_DATE, 'YYYY-MM-DD') DUE_DATE,
T.GROSS_AMOUNT,
T.CURR_CODE,
TO_CHAR(T.INVOICE_DATE, 'YYYY-MM-DD') INVOICE_DATE,
T.ACC_SEG3,
T.DESCRIPTION,
T.ATTRIBUTE3,
T.USER_NAME,
T.ATTRIBUTE2,
T.ATTRIBUTE4,
T.ATTRIBUTE5,
TO_CHAR(ROUND(SUM(T.AMOUNT_REMAINING), 2),
'fm99,999,999,999,999,990.00') TOTAL_AMOUNT,
TO_CHAR(ROUND(SUM(T.FUNC_AMOUNT_REMAINING), 2),
'fm99,999,999,999,999,990.00') TOTAL_AGING_AMOUNT
FROM (select f_invoice_id,
f_invoice_amount,
f_payment_status_flag,
f_invoice_type_lookup_code,
vendor_id,
vendor_type_disp,
vendor_name,
vendor_site_code,
invoice_type,
invoice_num,
invoice_id,
due_date,
gross_amount,
invoice_date,
description,
curr_code,
acc_seg3,
user_name,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
amount_remaining,
func_amount_remaining,
STATUS_FLAG
from t_Tmp_Ap_Status --我新建的过程中间表
) T
WHERE t.STATUS_FLAG NOT IN ('NO') --如果我想把这里的STATUS_FLAG字段，带到后续的结果集中，那么就需要带到最外层循环，发现跑不出来
GROUP BY GROUPING SETS((T.VENDOR_ID, STATUS_FLAG, --审批字段
T.VENDOR_TYPE_DISP, T.VENDOR_NAME, T.VENDOR_SITE_CODE, T.INVOICE_TYPE, T.INVOICE_NUM, T.INVOICE_ID, T.DUE_DATE, T.ACC_SEG3, T.GROSS_AMOUNT, T.CURR_CODE, T.INVOICE_DATE, T.DESCRIPTION, T.ATTRIBUTE3, T.USER_NAME, T.ATTRIBUTE2, T.ATTRIBUTE4, T.ATTRIBUTE5),(T.VENDOR_NAME),(T.VENDOR_NAME, T.VENDOR_SITE_CODE),())
HAVING SUM(T.FUNC_AMOUNT_REMAINING) <> 0) t
where VENDOR_ID is not null;

复制代码

添加状态字段居然跑不出来，不带字段 WHERE t.STATUS_FLAG NOT IN ('NO')且不在外层添加.STATUS_FLAG就能跑出来

后来我仔细分析了生成到t_a表的数据，发现amount金额为0的有30多w，于是在写入t_a表时直接进行过滤，然后在写入t_b表（5000条数据）时调用函数对每一条数据进行状态校验，然后在后续的处理逻辑里面直接使用t_b表并过滤很少的不满足审批状态的数据即可，整个接口运行耗时5分钟。