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

newkid

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

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

oracle_cj

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

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

1. SELECT T.VENDOR_ID,
   
2.        T.INVOICE_ID,
   
3.        STATUS_FLAG, --审批字段
   
4.        GROUPING_ID,
   
5.        VENDOR_TYPE_DISP,
   
6.        T.VENDOR_NAME,
   
7.        T.VENDOR_SITE_CODE,
   
8.        T.INVOICE_TYPE,
   
9.        T.INVOICE_NUM,
   
10.        DUE_DATE,
    
11.        T.GROSS_AMOUNT,
    
12.        T.CURR_CODE,
    
13.        INVOICE_DATE,
    
14.        T.ACC_SEG3,
    
15.        T.DESCRIPTION,
    
16.        T.ATTRIBUTE3,
    
17.        T.USER_NAME,
    
18.        T.ATTRIBUTE2,
    
19.        T.ATTRIBUTE4,
    
20.        T.ATTRIBUTE5,
    
21.        TRIM(REPLACE(T.TOTAL_AMOUNT, ',', '')),
    
22.        TRIM(REPLACE(T.TOTAL_AGING_AMOUNT, ',', ''))
    
23.   from (select T.VENDOR_ID,
    
24.                T.INVOICE_ID,
    
25.                STATUS_FLAG, --审批字段
    
26.                GROUPING_ID(T.VENDOR_ID,
    
27.                            STATUS_FLAG, --审批字段
    
28.                            T.VENDOR_TYPE_DISP,
    
29.                            T.VENDOR_NAME,
    
30.                            T.VENDOR_SITE_CODE,
    
31.                            T.INVOICE_TYPE,
    
32.                            T.INVOICE_NUM,
    
33.                            T.INVOICE_ID,
    
34.                            T.DUE_DATE,
    
35.                            T.ACC_SEG3) GROUPING_ID,
    
36.                DECODE(GROUPING_ID(T.VENDOR_ID,
    
37.                                   STATUS_FLAG, --审批字段
    
38.                                   T.VENDOR_TYPE_DISP,
    
39.                                   T.VENDOR_NAME,
    
40.                                   T.VENDOR_SITE_CODE,
    
41.                                   T.INVOICE_TYPE,
    
42.                                   T.INVOICE_NUM,
    
43.                                   T.INVOICE_ID,
    
44.                                   T.DUE_DATE,
    
45.                                   T.ACC_SEG3),
    
46.                       207,
    
47.                       '小计',
    
48.                       223,
    
49.                       '小计',
    
50.                       255,
    
51.                       '合计',
    
52.                       T.VENDOR_TYPE_DISP) VENDOR_TYPE_DISP,
    
53.                T.VENDOR_NAME,
    
54.                T.VENDOR_SITE_CODE,
    
55.                T.INVOICE_TYPE,
    
56.                T.INVOICE_NUM,
    
57.                TO_CHAR(T.DUE_DATE, 'YYYY-MM-DD') DUE_DATE,
    
58.                T.GROSS_AMOUNT,
    
59.                T.CURR_CODE,
    
60.                TO_CHAR(T.INVOICE_DATE, 'YYYY-MM-DD') INVOICE_DATE,
    
61.                T.ACC_SEG3,
    
62.                T.DESCRIPTION,
    
63.                T.ATTRIBUTE3,
    
64.                T.USER_NAME,
    
65.                T.ATTRIBUTE2,
    
66.                T.ATTRIBUTE4,
    
67.                T.ATTRIBUTE5,
    
68.                TO_CHAR(ROUND(SUM(T.AMOUNT_REMAINING), 2),
    
69.                        'fm99,999,999,999,999,990.00') TOTAL_AMOUNT,
    
70.                TO_CHAR(ROUND(SUM(T.FUNC_AMOUNT_REMAINING), 2),
    
71.                        'fm99,999,999,999,999,990.00') TOTAL_AGING_AMOUNT
    
72.           FROM (select f_invoice_id,
    
73.                        f_invoice_amount,
    
74.                        f_payment_status_flag,
    
75.                        f_invoice_type_lookup_code,
    
76.                        vendor_id,
    
77.                        vendor_type_disp,
    
78.                        vendor_name,
    
79.                        vendor_site_code,
    
80.                        invoice_type,
    
81.                        invoice_num,
    
82.                        invoice_id,
    
83.                        due_date,
    
84.                        gross_amount,
    
85.                        invoice_date,
    
86.                        description,
    
87.                        curr_code,
    
88.                        acc_seg3,
    
89.                        user_name,
    
90.                        attribute1,
    
91.                        attribute2,
    
92.                        attribute3,
    
93.                        attribute4,
    
94.                        attribute5,
    
95.                        amount_remaining,
    
96.                        func_amount_remaining,
    
97.                        STATUS_FLAG
    
98.                   from t_Tmp_Ap_Status --我新建的过程中间表
    
99.                 ) T
    
100.          WHERE t.STATUS_FLAG NOT IN ('NO') --如果我想把这里的STATUS_FLAG字段，带到后续的结果集中，那么就需要带到最外层循环，发现跑不出来
     
101.          GROUP BY GROUPING SETS((T.VENDOR_ID, STATUS_FLAG, --审批字段
     
102.                   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),())
     
103.         HAVING SUM(T.FUNC_AMOUNT_REMAINING) <> 0) t
     
104. where VENDOR_ID is not null;
     
105. 
     

复制代码

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

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