某系统巡检，发现TOPSQL里面第一条SQL语句的执行时间相当不合理~
select (select EC_CUST_NO
from GYL.FAAAAASM cus
where cus.refcode = a.SELLER_REFCODE) EC_CUST_NO,
BUSSTYPE,
sum(LOANAMT) SUMLOANAMT,
sum(OTSTAMT) SUMOTSTAMT,
sum(YPOTSTAMT) SUMYPOTSTAMT,
sum(YXCK) SUMYXCK,
sum(STARTAMT) SUMSTARTAMT,
sum(MARGINAAMT) SUMMARGINAAMT
from (select case
when typ.FACTSTATUS in ('P', 'G') then
buyer.BCHID
else
seller.BCHID
end BCHID,
case
when typ.FACTSTATUS in ('P', 'G') then
buyer.REFCODE
else
seller.REFCODE
end SELLER_REFCODE,
case
when typ.FACTSTATUS in ('P', 'G') then
buyerbch.BCHDESC
else
sellerbch.BCHDESC
end BCHDESC,
case
when typ.FACTSTATUS in ('P', 'G') then
buyerfbch.BCHDESC
else
sellerfbch.BCHDESC
end FBCHDESC,
bus.BUSSTYPE,
nvl(case
when (mas.LOANDATE >= To_date('20180916', 'YYYY-mm-dd') and
mas.LOANDATE <= To_date('20180916', 'YYYY-mm-dd')) then
mas.LOANAMT
else
0
end,
0) LOANAMT,
case
when mas.loanflag = '2' then
mas.OTSTAMT
else
0
end OTSTAMT,
case
when mas.loanflag = '1' then
mas.OTSTAMT
else
0
end YPOTSTAMT,
nvl((case
when (mas.OTSTAMT - nvl(gur.MARGINAAMT, 0)) < 0 then
0
else
(mas.OTSTAMT - nvl(gur.MARGINAAMT, 0))
end),
0) YXCK,
(select nvl(sum(nvl(dbam.DBALOANAMT, 0)), 0)
from GYL.FAAAAAAM dbam --14W
where dbam.APLREFCODE = mas.APPREFCODE
and dbam.ISTBSXDB = 'Y') STARTAMT,
nvl(gur_marginaamt.MARGINAAMT, 0) MARGINAAMT
from (select 1 as FLAG,
mase.DTC_REFCODE,
mase.REFCODE,
mase.VALUEDATE,
mase.LOANSUCCESSFLAG,
mase.CANCELFLAG,
mase.OTSTAMT,
mase.LOANFLAG,
mase.APPREFCODE,
mase.LOANAMT,
mase.LOANDATE
from GYL.FLAAAASE mase ---28W
where mase.seqno =
(select max(seqno)
from GYL.FLAAAASE newmase ---28W
where newmase.ECFLAG = '0'
and newmase.CANCELFLAG = '0'
and newmase.LOANSUCCESSFLAG = '1'
and newmase.REFCODE = mase.REFCODE
and newmase.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd'))
union all
select 2 as FLAG,
mase.DTC_REFCODE,
mase.REFCODE,
mase.VALUEDATE,
'1' as LOANSUCCESSFLAG,
'0' as CANCELFLAG,
mase.RENTLNAMT - nvl(stg.OTSTAMT, 0) OTSTAMT,
'2' as LOANFLAG,
0 as APPREFCODE,
mase.RENTLNAMT LOANAMT,
mase.VALUEDATE LOANDATE
from GYL.FLAAAATE mase -- 20
left join (select stge.LFBATREFCODE, sum(T_OSTAMT) OTSTAMT
from GYL.FLAAAAGE stge
where stge.seqno =
(select max(seqno)
from GYL.FLAAAAGE newstge
where newstge.REFCODE = stge.REFCODE
and newstge.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd')
and newstge.ECFLAG = '0')
group by stge.LFBATREFCODE) stg
on stg.LFBATREFCODE = mase.REFCODE
where mase.seqno =
(select max(seqno)
from GYL.FLAAAATE newmase--20
where newmase.ECFLAG = '0'
and newmase.REFCODE = mase.REFCODE
and newmase.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd'))
and mase.DTC_REFCODE not in
(select refcode
from GYL.FAAAAACM subdtc
where subdtc.BUSSTYPE in ('A021', 'A121'))) mas
left join GYL.FAAAAACM dtc
on dtc.REFCODE = mas.DTC_REFCODE
left join GYL.FAAAAASM seller
on seller.REFCODE = dtc.SELLER_REFCODE
left join GYL.FAAAAASM buyer
on buyer.REFCODE = dtc.BUYER_REFCODE
left join GYL.FAAAAAHM sellerbch
on sellerbch.BCHID = seller.BCHID
left join GYL.FAAAAAHM buyerbch
on buyerbch.BCHID = buyer.BCHID
left join GYL.FAAAAAHM sellerfbch
on sellerfbch.BCHID = seller.FBCHID
left join GYL.FAAAAAHM buyerfbch
on buyerfbch.BCHID = buyer.FBCHID
left join GYL.FIAAAASM bus
on bus.BUSSTYPE = dtc.BUSSTYPE
left join GYL.FIAAAAPM typ
on typ.TYPID = bus.TYPID
left join (select txrefcode, sum(MARGINAAMT) MARGINAAMT
from GYL.FMAAAARE gure --20W
where gure.EDTID <> 'DEL'
and gure.seqno =
(select max(seqno)
from GYL.FMAAAARE newgure --20W
where newgure.REFCODE = gure.REFCODE
and newgure.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd')
and newgure.DELFLAG = '0'
and newgure.ECFLAG = '0')
group by txrefcode) gur
on gur.txrefcode = mas.refcode
and mas.FLAG = 1
left join (select txrefcode, sum(MARGINAAMT) MARGINAAMT
from GYL.FMAAAARE gure --20W
where gure.EDTID <> 'DEL'
and gure.seqno =
(select max(seqno)
from GYL.FMAAAARE newgure --20W
where newgure.REFCODE = gure.REFCODE
and newgure.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd')
and newgure.DELFLAG = '0'
and newgure.ECFLAG = '0'
and gure.MARGINACCNNO is not null)
group by txrefcode) gur_marginaamt
on gur_marginaamt.txrefcode = mas.refcode
and mas.FLAG = 1
where 1 = 1
and mas.CANCELFLAG = '0'
and mas.LOANSUCCESSFLAG = '1'
and mas.VALUEDATE <= To_date('20180916', 'YYYY-mm-dd')) a
where 1 = 1
and a.BCHID in (SELECT BCHID FROM GYL.FAAAAAHM)
group by SELLER_REFCODE, BCHDESC, FBCHDESC, BUSSTYPE
order by SELLER_REFCODE, BCHDESC, FBCHDESC, BUSSTYPE;
Plan hash value: 395977065
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 266 | 91504 | | 1630K (1)| 05:26:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | FAAAAASM | 1 | 13 | | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | FAAAAASM_PK | 1 | | | 1 (0)| 00:00:01 |
| 3 | SORT GROUP BY | | 266 | 91504 | | 1630K (1)| 05:26:03 |
| 4 | NESTED LOOPS | | 266 | 91504 | | 1630K (1)| 05:26:03 |
|* 5 | HASH JOIN OUTER | | 266 | 89642 | | 1630K (1)| 05:26:03 |
|* 6 | HASH JOIN OUTER | | 266 | 84854 | | 1629K (1)| 05:25:52 |
|* 7 | HASH JOIN OUTER | | 266 | 74746 | | 1629K (1)| 05:25:52 |
|* 8 | HASH JOIN OUTER | | 266 | 64638 | | 1629K (1)| 05:25:52 |
|* 9 | HASH JOIN OUTER | | 266 | 60648 | | 1629K (1)| 05:25:50 |
|* 10 | HASH JOIN OUTER | | 266 | 50540 | | 1629K (1)| 05:25:50 |
|* 11 | HASH JOIN OUTER | | 266 | 40432 | | 1629K (1)| 05:25:50 |
|* 12 | HASH JOIN RIGHT OUTER | | 266 | 36442 | | 1628K (1)| 05:25:48 |
| 13 | TABLE ACCESS FULL | FIAAAAPM | 14 | 56 | | 3 (0)| 00:00:01 |
|* 14 | HASH JOIN RIGHT OUTER | | 266 | 35378 | | 1628K (1)| 05:25:48 |
| 15 | TABLE ACCESS FULL | FIAAAASM | 26 | 182 | | 3 (0)| 00:00:01 |
|* 16 | HASH JOIN OUTER | | 266 | 33516 | | 1628K (1)| 05:25:48 |
|* 17 | HASH JOIN OUTER | | 266 | 28728 | | 1628K (1)| 05:25:47 |
|* 18 | HASH JOIN RIGHT OUTER | | 266 | 23940 | | 1625K (1)| 05:25:12 |
| 19 | VIEW | | 1 | 26 | | 1618K (1)| 05:23:42 |
| 20 | HASH GROUP BY | | 1 | 40 | 11M| 1618K (1)| 05:23:42 |
|* 21 | FILTER | | | | | | |
|* 22 | TABLE ACCESS FULL | FMAAAARE | 204K| 7982K| | 1102 (1)| 00:00:14 |
| 23 | SORT AGGREGATE | | 1 | 21 | | | |
|* 24 | FILTER | | | | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | FMAAAARE | 5 | 105 | | 8 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | FMAAAARE_PK | 5 | | | 3 (0)| 00:00:01 |
| 27 | VIEW | | 266 | 17024 | | 7533 (2)| 00:01:31 |
| 28 | UNION-ALL | | | | | | |
|* 29 | HASH JOIN | | 265 | 19875 | | 7458 (2)| 00:01:30 |
| 30 | VIEW | VW_SQ_2 | 44616 | 1132K| | 4129 (2)| 00:00:50 |
| 31 | HASH GROUP BY | | 44616 | 1002K| 9616K| 4129 (2)| 00:00:50 |
|* 32 | TABLE ACCESS FULL | FLAAAASE | 244K| 5502K| | 3330 (2)| 00:00:40 |
|* 33 | TABLE ACCESS FULL | FLAAAASE | 247K| 11M| | 3326 (2)| 00:00:40 |
|* 34 | HASH JOIN OUTER | | 1 | 75 | | 75 (3)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 49 | | 74 (2)| 00:00:01 |
|* 36 | HASH JOIN ANTI SNA | | 1 | 36 | | 72 (2)| 00:00:01 |
|* 37 | TABLE ACCESS FULL | FLAAAATE | 19 | 494 | | 3 (0)| 00:00:01 |
|* 38 | TABLE ACCESS FULL | FAAAAACM | 7 | 70 | | 68 (0)| 00:00:01 |
|* 39 | VIEW PUSHED PREDICATE | VW_SQ_4 | 1 | 13 | | 2 (0)| 00:00:01 |
|* 40 | FILTER | | | | | | |
| 41 | SORT AGGREGATE | | 1 | 17 | | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | FLAAAATE | 1 | 17 | | 2 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | FLAAAATE_PK | 1 | | | 1 (0)| 00:00:01 |
| 44 | VIEW | | 1 | 26 | | 1 (100)| 00:00:01 |
| 45 | HASH GROUP BY | | 1 | 78 | | 1 (100)| 00:00:01 |
| 46 | NESTED LOOPS | | | | | | |
| 47 | NESTED LOOPS | | 1 | 78 | | 0 (0)| 00:00:01 |
| 48 | VIEW | VW_SQ_3 | 1 | 26 | | 0 (0)| 00:00:01 |
| 49 | HASH GROUP BY | | 1 | 38 | | 0 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID| FLAAAAGE | 1 | 38 | | 0 (0)| 00:00:01 |
| 51 | INDEX FULL SCAN | FLAAAAGE_PK | 1 | | | 0 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | FLAAAAGE_PK | 1 | | | 0 (0)| 00:00:01 |
| 53 | TABLE ACCESS BY INDEX ROWID | FLAAAAGE | 1 | 52 | | 0 (0)| 00:00:01 |
| 54 | VIEW | | 868 | 15624 | | 2875 (2)| 00:00:35 |
| 55 | HASH GROUP BY | | 868 | 34720 | | 2875 (2)| 00:00:35 |
|* 56 | HASH JOIN | | 868 | 34720 | | 2874 (2)| 00:00:35 |
| 57 | VIEW | VW_SQ_1 | 42840 | 753K| | 1770 (3)| 00:00:22 |
| 58 | HASH GROUP BY | | 42840 | 878K| 7288K| 1770 (3)| 00:00:22 |
|* 59 | TABLE ACCESS FULL | FMAAAARE | 205K| 4214K| | 1114 (3)| 00:00:14 |
|* 60 | TABLE ACCESS FULL | FMAAAARE | 204K| 4390K| | 1102 (1)| 00:00:14 |
| 61 | TABLE ACCESS FULL | FAAAAACM | 3501 | 63018 | | 68 (0)| 00:00:01 |
| 62 | TABLE ACCESS FULL | FAAAAASM | 6051 | 90765 | | 171 (1)| 00:00:03 |
| 63 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 64 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 65 | TABLE ACCESS FULL | FAAAAASM | 6051 | 90765 | | 171 (1)| 00:00:03 |
| 66 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 67 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 68 | VIEW | | 34882 | 613K| | 866 (2)| 00:00:11 |
| 69 | HASH GROUP BY | | 34882 | 374K| | 866 (2)| 00:00:11 |
|* 70 | TABLE ACCESS FULL | FAAAAAAM | 48431 | 520K| | 862 (2)| 00:00:11 |
|* 71 | INDEX UNIQUE SCAN | FAAAAAHM_PK | 1 | 7 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUS"."REFCODE"=:B1)
5 - access("DBAM"."APLREFCODE"(+)="MAS"."APPREFCODE")
6 - access("BUYERFBCH"."BCHID"(+)="BUYER"."FBCHID")
7 - access("BUYERBCH"."BCHID"(+)="BUYER"."BCHID")
8 - access("BUYER"."REFCODE"(+)="DTC"."BUYER_REFCODE")
9 - access("SELLERFBCH"."BCHID"(+)="SELLER"."FBCHID")
10 - access("SELLERBCH"."BCHID"(+)="SELLER"."BCHID")
11 - access("SELLER"."REFCODE"(+)="DTC"."SELLER_REFCODE")
12 - access("TYP"."TYPID"(+)="BUS"."TYPID")
14 - access("BUS"."BUSSTYPE"(+)="DTC"."BUSSTYPE")
16 - access("DTC"."REFCODE"(+)="MAS"."DTC_REFCODE")
17 - access("GUR"."TXREFCODE"(+)="MAS"."REFCODE" AND "MAS"."FLAG"=CASE WHEN ("GUR"."TXREFCODE"(+) IS NOT NULL)
THEN 1 ELSE 1 END )
18 - access("GUR_MARGINAAMT"."TXREFCODE"(+)="MAS"."REFCODE" AND "MAS"."FLAG"=CASE WHEN
("GUR_MARGINAAMT"."TXREFCODE"(+) IS NOT NULL) THEN 1 ELSE 1 END )
21 - filter("GURE"."SEQNO"= (SELECT MAX("SEQNO") FROM "GYL"."FMAAAARE" "NEWGURE" WHERE :B1 IS NOT NULL AND
"NEWGURE"."REFCODE"=:B2 AND "NEWGURE"."DELFLAG"='0' AND "NEWGURE"."ECFLAG"='0' AND
"NEWGURE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd')))
22 - filter("GURE"."EDTID"<>U'DEL')
24 - filter(:B1 IS NOT NULL)
25 - filter("NEWGURE"."DELFLAG"='0' AND "NEWGURE"."ECFLAG"='0' AND
"NEWGURE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
26 - access("NEWGURE"."REFCODE"=:B1)
29 - access("MASE"."SEQNO"="MAX(SEQNO)" AND "ITEM_2"="MASE"."REFCODE")
32 - filter("NEWMASE"."LOANSUCCESSFLAG"='1' AND "NEWMASE"."ECFLAG"='0' AND "NEWMASE"."CANCELFLAG"='0' AND
"NEWMASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
33 - filter("MASE"."LOANSUCCESSFLAG"='1' AND "MASE"."CANCELFLAG"='0' AND
"MASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
34 - access("STG"."LFBATREFCODE"(+)="MASE"."REFCODE")
36 - access("MASE"."DTC_REFCODE"="REFCODE")
37 - filter("MASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
38 - filter("SUBDTC"."BUSSTYPE"=U'A021' OR "SUBDTC"."BUSSTYPE"=U'A121')
39 - filter("MASE"."SEQNO"="MAX(SEQNO)")
40 - filter(COUNT(*)>0 AND '0'='0' AND '1'='1')
42 - filter("NEWMASE"."ECFLAG"='0' AND "NEWMASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
43 - access("NEWMASE"."REFCODE"="MASE"."REFCODE")
50 - filter("NEWSTGE"."ECFLAG"='0' AND "NEWSTGE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
52 - access("ITEM_3"="STGE"."REFCODE" AND "STGE"."SEQNO"="MAX(SEQNO)")
56 - access("GURE"."SEQNO"="MAX(SEQNO)" AND "ITEM_1"="GURE"."REFCODE")
59 - filter("NEWGURE"."DELFLAG"='0' AND "NEWGURE"."ECFLAG"='0' AND
"NEWGURE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
60 - filter("GURE"."EDTID"<>U'DEL')
70 - filter("ISTBSXDB"='Y')
71 - access("BCHID"=CASE WHEN (("TYP"."FACTSTATUS"='P') OR ("TYP"."FACTSTATUS"='G')) THEN "BUYER"."BCHID" ELSE
"SELLER"."BCHID" END )使用之前的脚本抓取SQL中的对象信息如下：
OWNER OBJECT_TYPE OBJECT_NAME PARTITIONED SIZE_MB NUM_ROWS LAST_ANALYZED STATUS
---------------------- --------------- ----------- ---------- ---------- ------------- ---------------------
GYL TABLE FAAAAAAM NO 26 143894 2018/6/26 22: 统计信息未过期
GYL TABLE FAAAAASM NO 5 6051 2018/9/16 14: 统计信息未过期
GYL TABLE FAAAAACM NO 2 3501 2018/8/28 22: 统计信息未过期
GYL TABLE FAAAAAHM NO 0.3125 2166 2018/3/28 22: 统计信息未过期
GYL TABLE FIAAAASM NO 0.0625 26 2017/9/14 22: 统计信息未过期
GYL TABLE FIAAAAPM NO 0.0625 14 2017/9/14 22: 统计信息未过期
GYL TABLE FLAAAATE NO 0.0625 19 2017/6/30 22: 统计信息未过期
GYL TABLE FLAAAAGE NO 0.0625 0 2013/7/26 3:1 统计信息未过期
GYL TABLE FLAAAASE NO 96 288041 2018/9/6 22:0 统计信息未过期
GYL TABLE FMAAAARE NO 34 205634 2018/6/13 22: 统计信息未过期
GYL INDEX (UNIQUE) FAAAAASM_PK NO 0.1875 6051 2018/9/16 14: 统计信息未过期
GYL INDEX (UNIQUE) FAAAAAHM_PK NO 0.125 2166 2018/3/28 22: 统计信息未过期
GYL INDEX (UNIQUE) FLAAAATE_PK NO 0.0625 19 2017/6/30 22: 统计信息未过期
GYL INDEX (UNIQUE) FLAAAAGE_PK NO 0.0625 0 2013/7/26 3:1 统计信息未过期
GYL INDEX (UNIQUE) FMAAAARE_PK NO 7 205634 2018/6/13 22: 统计信息未过期
15 rows selected表统计信息没有过期，而且最大的表也不到30w行。这样一条语句居然执行了30分钟~
针对这种SQL长，执行计划也长的SQL应该怎么快速定位性能瓶颈？
我之前的博客里面一直推崇的方法就是：找大表，好像不太适用。但是也可以用，只不过需要多找几步
我们今天换一种方法：找坑~也就是可能引起性能隐患的地方。其实我前面的博客提过 这种点不多：标量子查询、自定义函数、笛卡尔积、FILTER、嵌套循环（驱动表统计信息过期的时候）……
通过观察SQL，我们很容易发现标量子查询：
还不知道标量子查询是什么的，我之前的博客里面有详细讲解https://blog.csdn.net/Skybig1988/article/details/71171701
SQL语句的第1行和第63行，标量子查询的原理就是外循环返回的每一行 到内循环去匹配，针对这两个标量子查询 外循环返回的行数是一致的。主要看内循环 第一行这个FAAAAASM表很小 只有5MB ,第63行这个表FAAAAAAM大小26MB,如果外循环返回10000行，内循环里面FAAAAASM和 FAAAAAAM会被全表扫描10000次.第63行这个表消耗的资源是第一行的标量所消耗资源的好几倍。先干掉性能瓶颈：我们就先改写第63行的标量，如果还不行我们再改写第1行的标量。改写完的SQL和执行计划如下：
select (select EC_CUST_NO
from GYL.FAAAAASM cus
where cus.refcode = a.SELLER_REFCODE) EC_CUST_NO,
BUSSTYPE,
sum(LOANAMT) SUMLOANAMT,
sum(OTSTAMT) SUMOTSTAMT,
sum(YPOTSTAMT) SUMYPOTSTAMT,
sum(YXCK) SUMYXCK,
sum(STARTAMT) SUMSTARTAMT,
sum(MARGINAAMT) SUMMARGINAAMT
from (select case
when typ.FACTSTATUS in ('P', 'G') then
buyer.BCHID
else
seller.BCHID
end BCHID,
case
when typ.FACTSTATUS in ('P', 'G') then
buyer.REFCODE
else
seller.REFCODE
end SELLER_REFCODE,
case
when typ.FACTSTATUS in ('P', 'G') then
buyerbch.BCHDESC
else
sellerbch.BCHDESC
end BCHDESC,
case
when typ.FACTSTATUS in ('P', 'G') then
buyerfbch.BCHDESC
else
sellerfbch.BCHDESC
end FBCHDESC,
bus.BUSSTYPE,
nvl(case
when (mas.LOANDATE >= To_date('20180916', 'YYYY-mm-dd') and
mas.LOANDATE <= To_date('20180916', 'YYYY-mm-dd')) then
mas.LOANAMT
else
0
end,
0) LOANAMT,
case
when mas.loanflag = '2' then
mas.OTSTAMT
else
0
end OTSTAMT,
case
when mas.loanflag = '1' then
mas.OTSTAMT
else
0
end YPOTSTAMT,
nvl((case
when (mas.OTSTAMT - nvl(gur.MARGINAAMT, 0)) < 0 then
0
else
(mas.OTSTAMT - nvl(gur.MARGINAAMT, 0))
end),
0) YXCK,
dbam.STARTAMT,
nvl(gur_marginaamt.MARGINAAMT, 0) MARGINAAMT
from (select 1 as FLAG,
mase.DTC_REFCODE,
mase.REFCODE,
mase.VALUEDATE,
mase.LOANSUCCESSFLAG,
mase.CANCELFLAG,
mase.OTSTAMT,
mase.LOANFLAG,
mase.APPREFCODE,
mase.LOANAMT,
mase.LOANDATE
from GYL.FLAAAASE mase ---28W
where mase.seqno =
(select max(seqno)
from GYL.FLAAAASE newmase ---28W
where newmase.ECFLAG = '0'
and newmase.CANCELFLAG = '0'
and newmase.LOANSUCCESSFLAG = '1'
and newmase.REFCODE = mase.REFCODE
and newmase.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd'))
union all
select 2 as FLAG,
mase.DTC_REFCODE,
mase.REFCODE,
mase.VALUEDATE,
'1' as LOANSUCCESSFLAG,
'0' as CANCELFLAG,
mase.RENTLNAMT - nvl(stg.OTSTAMT, 0) OTSTAMT,
'2' as LOANFLAG,
0 as APPREFCODE,
mase.RENTLNAMT LOANAMT,
mase.VALUEDATE LOANDATE
from GYL.FLAAAATE mase -- 20
left join (select stge.LFBATREFCODE, sum(T_OSTAMT) OTSTAMT
from GYL.FLAAAAGE stge
where stge.seqno =
(select max(seqno)
from GYL.FLAAAAGE newstge
where newstge.REFCODE = stge.REFCODE
and newstge.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd')
and newstge.ECFLAG = '0')
group by stge.LFBATREFCODE) stg
on stg.LFBATREFCODE = mase.REFCODE
where mase.seqno =
(select max(seqno)
from GYL.FLAAAATE newmase--20
where newmase.ECFLAG = '0'
and newmase.REFCODE = mase.REFCODE
and newmase.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd'))
and mase.DTC_REFCODE not in
(select refcode
from GYL.FAAAAACM subdtc
where subdtc.BUSSTYPE in ('A021', 'A121'))) mas
left join (select APLREFCODE,nvl(sum(nvl(DBALOANAMT, 0)), 0) STARTAMT ---标量子查询改成外连接
from GYL.FAAAAAAM --14W
where ISTBSXDB = 'Y' group by APLREFCODE
) dbam on dbam.APLREFCODE = mas.APPREFCODE
left join GYL.FAAAAACM dtc
on dtc.REFCODE = mas.DTC_REFCODE
left join GYL.FAAAAASM seller
on seller.REFCODE = dtc.SELLER_REFCODE
left join GYL.FAAAAASM buyer
on buyer.REFCODE = dtc.BUYER_REFCODE
left join GYL.FAAAAAHM sellerbch
on sellerbch.BCHID = seller.BCHID
left join GYL.FAAAAAHM buyerbch
on buyerbch.BCHID = buyer.BCHID
left join GYL.FAAAAAHM sellerfbch
on sellerfbch.BCHID = seller.FBCHID
left join GYL.FAAAAAHM buyerfbch
on buyerfbch.BCHID = buyer.FBCHID
left join GYL.FIAAAASM bus
on bus.BUSSTYPE = dtc.BUSSTYPE
left join GYL.FIAAAAPM typ
on typ.TYPID = bus.TYPID
left join (select txrefcode, sum(MARGINAAMT) MARGINAAMT
from GYL.FMAAAARE gure --20W
where gure.EDTID <> 'DEL'
and gure.seqno =
(select max(seqno)
from GYL.FMAAAARE newgure --20W
where newgure.REFCODE = gure.REFCODE
and newgure.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd')
and newgure.DELFLAG = '0'
and newgure.ECFLAG = '0')
group by txrefcode) gur
on gur.txrefcode = mas.refcode
and mas.FLAG = 1
left join (select txrefcode, sum(MARGINAAMT) MARGINAAMT
from GYL.FMAAAARE gure --20W
where gure.EDTID <> 'DEL'
and gure.seqno =
(select max(seqno)
from GYL.FMAAAARE newgure --20W
where newgure.REFCODE = gure.REFCODE
and newgure.VALUEDATE <=
To_date('20180916', 'YYYY-mm-dd')
and newgure.DELFLAG = '0'
and newgure.ECFLAG = '0'
and gure.MARGINACCNNO is not null)
group by txrefcode) gur_marginaamt
on gur_marginaamt.txrefcode = mas.refcode
and mas.FLAG = 1
where 1 = 1
and mas.CANCELFLAG = '0'
and mas.LOANSUCCESSFLAG = '1'
and mas.VALUEDATE <= To_date('20180916', 'YYYY-mm-dd')) a
where 1 = 1
and a.BCHID in (SELECT BCHID FROM GYL.FAAAAAHM)
group by SELLER_REFCODE, BCHDESC, FBCHDESC, BUSSTYPE
order by SELLER_REFCODE, BCHDESC, FBCHDESC, BUSSTYPE;
Plan hash value: 395977065
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 266 | 91504 | | 1630K (1)| 05:26:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | FAAAAASM | 1 | 13 | | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | FAAAAASM_PK | 1 | | | 1 (0)| 00:00:01 |
| 3 | SORT GROUP BY | | 266 | 91504 | | 1630K (1)| 05:26:03 |
| 4 | NESTED LOOPS | | 266 | 91504 | | 1630K (1)| 05:26:03 |
|* 5 | HASH JOIN OUTER | | 266 | 89642 | | 1630K (1)| 05:26:03 |
|* 6 | HASH JOIN OUTER | | 266 | 84854 | | 1629K (1)| 05:25:52 |
|* 7 | HASH JOIN OUTER | | 266 | 74746 | | 1629K (1)| 05:25:52 |
|* 8 | HASH JOIN OUTER | | 266 | 64638 | | 1629K (1)| 05:25:52 |
|* 9 | HASH JOIN OUTER | | 266 | 60648 | | 1629K (1)| 05:25:50 |
|* 10 | HASH JOIN OUTER | | 266 | 50540 | | 1629K (1)| 05:25:50 |
|* 11 | HASH JOIN OUTER | | 266 | 40432 | | 1629K (1)| 05:25:50 |
|* 12 | HASH JOIN RIGHT OUTER | | 266 | 36442 | | 1628K (1)| 05:25:48 |
| 13 | TABLE ACCESS FULL | FIAAAAPM | 14 | 56 | | 3 (0)| 00:00:01 |
|* 14 | HASH JOIN RIGHT OUTER | | 266 | 35378 | | 1628K (1)| 05:25:48 |
| 15 | TABLE ACCESS FULL | FIAAAASM | 26 | 182 | | 3 (0)| 00:00:01 |
|* 16 | HASH JOIN OUTER | | 266 | 33516 | | 1628K (1)| 05:25:48 |
|* 17 | HASH JOIN OUTER | | 266 | 28728 | | 1628K (1)| 05:25:47 |
|* 18 | HASH JOIN RIGHT OUTER | | 266 | 23940 | | 1625K (1)| 05:25:12 |
| 19 | VIEW | | 1 | 26 | | 1618K (1)| 05:23:42 |
| 20 | HASH GROUP BY | | 1 | 40 | 11M| 1618K (1)| 05:23:42 |
|* 21 | FILTER | | | | | | |
|* 22 | TABLE ACCESS FULL | FMAAAARE | 204K| 7982K| | 1102 (1)| 00:00:14 |
| 23 | SORT AGGREGATE | | 1 | 21 | | | |
|* 24 | FILTER | | | | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | FMAAAARE | 5 | 105 | | 8 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | FMAAAARE_PK | 5 | | | 3 (0)| 00:00:01 |
| 27 | VIEW | | 266 | 17024 | | 7533 (2)| 00:01:31 |
| 28 | UNION-ALL | | | | | | |
|* 29 | HASH JOIN | | 265 | 19875 | | 7458 (2)| 00:01:30 |
| 30 | VIEW | VW_SQ_2 | 44616 | 1132K| | 4129 (2)| 00:00:50 |
| 31 | HASH GROUP BY | | 44616 | 1002K| 9616K| 4129 (2)| 00:00:50 |
|* 32 | TABLE ACCESS FULL | FLAAAASE | 244K| 5502K| | 3330 (2)| 00:00:40 |
|* 33 | TABLE ACCESS FULL | FLAAAASE | 247K| 11M| | 3326 (2)| 00:00:40 |
|* 34 | HASH JOIN OUTER | | 1 | 75 | | 75 (3)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 49 | | 74 (2)| 00:00:01 |
|* 36 | HASH JOIN ANTI SNA | | 1 | 36 | | 72 (2)| 00:00:01 |
|* 37 | TABLE ACCESS FULL | FLAAAATE | 19 | 494 | | 3 (0)| 00:00:01 |
|* 38 | TABLE ACCESS FULL | FAAAAACM | 7 | 70 | | 68 (0)| 00:00:01 |
|* 39 | VIEW PUSHED PREDICATE | VW_SQ_4 | 1 | 13 | | 2 (0)| 00:00:01 |
|* 40 | FILTER | | | | | | |
| 41 | SORT AGGREGATE | | 1 | 17 | | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | FLAAAATE | 1 | 17 | | 2 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | FLAAAATE_PK | 1 | | | 1 (0)| 00:00:01 |
| 44 | VIEW | | 1 | 26 | | 1 (100)| 00:00:01 |
| 45 | HASH GROUP BY | | 1 | 78 | | 1 (100)| 00:00:01 |
| 46 | NESTED LOOPS | | | | | | |
| 47 | NESTED LOOPS | | 1 | 78 | | 0 (0)| 00:00:01 |
| 48 | VIEW | VW_SQ_3 | 1 | 26 | | 0 (0)| 00:00:01 |
| 49 | HASH GROUP BY | | 1 | 38 | | 0 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID| FLAAAAGE | 1 | 38 | | 0 (0)| 00:00:01 |
| 51 | INDEX FULL SCAN | FLAAAAGE_PK | 1 | | | 0 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | FLAAAAGE_PK | 1 | | | 0 (0)| 00:00:01 |
| 53 | TABLE ACCESS BY INDEX ROWID | FLAAAAGE | 1 | 52 | | 0 (0)| 00:00:01 |
| 54 | VIEW | | 868 | 15624 | | 2875 (2)| 00:00:35 |
| 55 | HASH GROUP BY | | 868 | 34720 | | 2875 (2)| 00:00:35 |
|* 56 | HASH JOIN | | 868 | 34720 | | 2874 (2)| 00:00:35 |
| 57 | VIEW | VW_SQ_1 | 42840 | 753K| | 1770 (3)| 00:00:22 |
| 58 | HASH GROUP BY | | 42840 | 878K| 7288K| 1770 (3)| 00:00:22 |
|* 59 | TABLE ACCESS FULL | FMAAAARE | 205K| 4214K| | 1114 (3)| 00:00:14 |
|* 60 | TABLE ACCESS FULL | FMAAAARE | 204K| 4390K| | 1102 (1)| 00:00:14 |
| 61 | TABLE ACCESS FULL | FAAAAACM | 3501 | 63018 | | 68 (0)| 00:00:01 |
| 62 | TABLE ACCESS FULL | FAAAAASM | 6051 | 90765 | | 171 (1)| 00:00:03 |
| 63 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 64 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 65 | TABLE ACCESS FULL | FAAAAASM | 6051 | 90765 | | 171 (1)| 00:00:03 |
| 66 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 67 | TABLE ACCESS FULL | FAAAAAHM | 2166 | 82308 | | 11 (0)| 00:00:01 |
| 68 | VIEW | | 34882 | 613K| | 866 (2)| 00:00:11 |
| 69 | HASH GROUP BY | | 34882 | 374K| | 866 (2)| 00:00:11 |
|* 70 | TABLE ACCESS FULL | FAAAAAAM | 48431 | 520K| | 862 (2)| 00:00:11 |
|* 71 | INDEX UNIQUE SCAN | FAAAAAHM_PK | 1 | 7 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUS"."REFCODE"=:B1)
5 - access("DBAM"."APLREFCODE"(+)="MAS"."APPREFCODE")
6 - access("BUYERFBCH"."BCHID"(+)="BUYER"."FBCHID")
7 - access("BUYERBCH"."BCHID"(+)="BUYER"."BCHID")
8 - access("BUYER"."REFCODE"(+)="DTC"."BUYER_REFCODE")
9 - access("SELLERFBCH"."BCHID"(+)="SELLER"."FBCHID")
10 - access("SELLERBCH"."BCHID"(+)="SELLER"."BCHID")
11 - access("SELLER"."REFCODE"(+)="DTC"."SELLER_REFCODE")
12 - access("TYP"."TYPID"(+)="BUS"."TYPID")
14 - access("BUS"."BUSSTYPE"(+)="DTC"."BUSSTYPE")
16 - access("DTC"."REFCODE"(+)="MAS"."DTC_REFCODE")
17 - access("GUR"."TXREFCODE"(+)="MAS"."REFCODE" AND "MAS"."FLAG"=CASE WHEN ("GUR"."TXREFCODE"(+) IS NOT NULL)
THEN 1 ELSE 1 END )
18 - access("GUR_MARGINAAMT"."TXREFCODE"(+)="MAS"."REFCODE" AND "MAS"."FLAG"=CASE WHEN
("GUR_MARGINAAMT"."TXREFCODE"(+) IS NOT NULL) THEN 1 ELSE 1 END )
21 - filter("GURE"."SEQNO"= (SELECT MAX("SEQNO") FROM "GYL"."FMAAAARE" "NEWGURE" WHERE :B1 IS NOT NULL AND
"NEWGURE"."REFCODE"=:B2 AND "NEWGURE"."DELFLAG"='0' AND "NEWGURE"."ECFLAG"='0' AND
"NEWGURE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd')))
22 - filter("GURE"."EDTID"<>U'DEL')
24 - filter(:B1 IS NOT NULL)
25 - filter("NEWGURE"."DELFLAG"='0' AND "NEWGURE"."ECFLAG"='0' AND
"NEWGURE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
26 - access("NEWGURE"."REFCODE"=:B1)
29 - access("MASE"."SEQNO"="MAX(SEQNO)" AND "ITEM_2"="MASE"."REFCODE")
32 - filter("NEWMASE"."LOANSUCCESSFLAG"='1' AND "NEWMASE"."ECFLAG"='0' AND "NEWMASE"."CANCELFLAG"='0' AND
"NEWMASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
33 - filter("MASE"."LOANSUCCESSFLAG"='1' AND "MASE"."CANCELFLAG"='0' AND
"MASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
34 - access("STG"."LFBATREFCODE"(+)="MASE"."REFCODE")
36 - access("MASE"."DTC_REFCODE"="REFCODE")
37 - filter("MASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
38 - filter("SUBDTC"."BUSSTYPE"=U'A021' OR "SUBDTC"."BUSSTYPE"=U'A121')
39 - filter("MASE"."SEQNO"="MAX(SEQNO)")
40 - filter(COUNT(*)>0 AND '0'='0' AND '1'='1')
42 - filter("NEWMASE"."ECFLAG"='0' AND "NEWMASE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
43 - access("NEWMASE"."REFCODE"="MASE"."REFCODE")
50 - filter("NEWSTGE"."ECFLAG"='0' AND "NEWSTGE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
52 - access("ITEM_3"="STGE"."REFCODE" AND "STGE"."SEQNO"="MAX(SEQNO)")
56 - access("GURE"."SEQNO"="MAX(SEQNO)" AND "ITEM_1"="GURE"."REFCODE")
59 - filter("NEWGURE"."DELFLAG"='0' AND "NEWGURE"."ECFLAG"='0' AND
"NEWGURE"."VALUEDATE"<=TO_DATE('20180916','YYYY-mm-dd'))
60 - filter("GURE"."EDTID"<>U'DEL')
70 - filter("ISTBSXDB"='Y')
71 - access("BCHID"=CASE WHEN (("TYP"."FACTSTATUS"='P') OR ("TYP"."FACTSTATUS"='G')) THEN "BUYER"."BCHID" ELSE
"SELLER"."BCHID" END )改写完之后 SQL2分钟出结果，达到要求！！！！如果有强迫症可以把第一行的标量也改了，这样SQL的运行效率还会有少许提升~
扫一扫
1550
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
