explain plan for
SELECT
date '2014-03-31',
b.cust_no 客户编号,
b.cust_name 客户名称,
b.balance AS 贷款余额,
b.changkou 授信敞口金额,
(CASE
WHEN b.changkou <= 500000 THEN
'微贷客户'
WHEN b.changkou > 500000
AND b.changkou <= 2000000 THEN
'核心小微客户'
WHEN b.changkou < = 5000000
AND b.changkou > 2000000 THEN
'小微客户'
WHEN b.changkou > 5000000
AND b.changkou < = 50000000 THEN
'中型客户'
WHEN b.changkou > 50000000 THEN
'大型客户'
END) 大中小标志
FROM (SELECT aa.cust_no, --客户编号
aa.cust_name,
SUM((aa.balance * nvl(aa.rate, 1)) / 10000) AS balance,
nvl(fun_get_cust_ck(aa.cust_no, '20140331'), 0) changkou --函数调用
FROM (SELECT
bd.cust_no, --客户编号
bc.cust_name,
h.rate,
bd.balance
FROM dwf.f_agt_business_duebill_h bd
INNER JOIN dwf.f_agt_business_contract_h bc
ON bc.agmt_id = bd.relativeserialno2
AND bc.businesstype LIKE '1%'
AND bc.corp_org = bd.corp_org
AND bc.start_dt <= date '2014-03-31' --查询日期
AND bc.end_dt > date '2014-03-31'
LEFT JOIN b_m_com_cur_tran_rate h --汇率转换表
ON h.tx_date = date '2014-03-31' --当天日期
AND bd.businesscurrency = h.cur_cd --币种
AND h.trans_cur_cd = 'T00RMB2' --直接折人民币(汇总人民币)
WHERE bd.balance >= 0
AND bd.host_org_no IN
(SELECT t.org_id
FROM b_m_sys_branch t
WHERE t.status = 1
AND t.dept_flag != '2'
CONNECT BY PRIOR t.id = t.parent_id
START WITH t.org_id = '10000')
AND bd.start_dt <= date '2014-03-31' --查询日期
AND bd.end_dt > date '2014-03-31'
) aa
WHERE aa.balance > 0
GROUP BY aa.cust_no, aa.cust_name) b
主SQL执行一次:
这条SQL执行了885次
SELECT SUM(FUN_BUSINESS_CK2('BusinessContract', BC.AGMT_ID, :B1 )) FROM DWF.F_AGT_BUSINESS_CONTRACT_H BC WHERE
((BC.BUSINESSTYPE NOT IN ('1020020', '1020040') AND BC.FINISHDATE IS NULL) OR BC.BUSINESSTYPE = '2020' OR (BC.BUSINESSTYPE IN ('1020020', '1020040') AND BC.BILLTYPE IN ('0', '2')) ) AND BC.TDAY_BAL IS NOT NULL AND BC.CUST_NO = :B2 AND BC.START_DT<= TO_DATE(:B1 , 'YYYY-MM-DD') AND BC.END_DT > TO_DATE(:B1 , 'YYYY-MM-DD') AND BC.CORP_ORG='15601' GROUP BY BC.CUST_NO
这条SQL执行了2974次
SELECT A.TDAY_BAL * NVL(H1.RATE, 1), NVL(A.AMT, 0) * NVL(H1.RATE, 1), NVL(A.BAIL_AMT, 0), A.BUSINESSTYPE, A.OCCURTYPE, NVL(A.BILL_COUNT, 1) FROM DWF.F_AGT_BUSINESS_CONTRACT_H A LEFT JOIN B_M_COM_CUR_TRAN_RATE H1 ON H1.TX_DATE = TO_DATE(:B1 , 'YYYY-MM-DD') AND A.CUR_CD = H1.CUR_CD AND H1.TRANS_CUR_CD = 'T00RMB2' WHERE A.AGMT_ID = :B2 AND A.CORP_ORG = '15601' AND A.START_DT <= TO_DATE(:B1 , 'YYYY-MM-DD') AND A.END_DT > TO_DATE(:B1 , 'YYYY-MM-DD')
查看函数里SQL的执行计划:
SQL_ID gyv4dxzkxvqhy, child number 0
-------------------------------------
SELECT SUM(FUN_BUSINESS_CK2('BusinessContract', BC.AGMT_ID,:B1 )) FROM
DWF.F_AGT_BUSINESS_CONTRACT_H BC WHERE ((BC.BUSINESSTYPE NOT IN
('1020020', '1020040') AND BC.FINISHDATE IS NULL) OR BC.BUSINESSTYPE =
'2020' OR (BC.BUSINESSTYPE IN ('1020020', '1020040') AND BC.BILLTYPE IN
('0', '2')) ) AND BC.TDAY_BAL IS NOT NULL AND BC.CUST_NO = :B2 AND
BC.START_DT<= TO_DATE(:B1 , 'YYYY-MM-DD') AND BC.END_DT > TO_DATE(:B1 ,
'YYYY-MM-DD') AND BC.CORP_ORG='15601' GROUP BY BC.CUST_NO
Plan hash value: 3985093115
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 17056 (100)| |
| 1 | SORT GROUP BY NOSORT | | 1 | 74 | 17056 (1)| 00:03:25 |
|* 2 | TABLE ACCESS BY INDEX ROWID| F_AGT_BUSINESS_CONTRACT_H | 1 | 74 | 17056 (1)| 00:03:25 |
|* 3 | INDEX SKIP SCAN | F_AGT_BUSINESS_CONTRACT_H_IDX5 | 1 | | 17055 (1)| 00:03:25 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(((("BC"."FINISHDATE" IS NULL AND "BC"."BUSINESSTYPE"<>'1020020' AND
"BC"."BUSINESSTYPE"<>'1020040') OR "BC"."BUSINESSTYPE"='2020' OR (INTERNAL_FUNCTION("BC"."BILLTYPE")
AND INTERNAL_FUNCTION("BC"."BUSINESSTYPE"))) AND "BC"."TDAY_BAL" IS NOT NULL))
3 - access("BC"."END_DT">TO_DATE(:B1,'YYYY-MM-DD') AND "BC"."CORP_ORG"='15601' AND
"BC"."CUST_NO"=:B2 AND "BC"."START_DT"<=TO_DATE(:B1,'YYYY-MM-DD'))
filter(("BC"."CUST_NO"=:B2 AND "BC"."CORP_ORG"='15601' AND
"BC"."END_DT">TO_DATE(:B1,'YYYY-MM-DD') AND "BC"."START_DT"<=TO_DATE(:B1,'YYYY-MM-DD')))
SQL_ID 1hunvxuy53xau, child number 0
-------------------------------------
SELECT A.TDAY_BAL * NVL(H1.RATE, 1), NVL(A.AMT, 0) * NVL(H1.RATE, 1),
NVL(A.BAIL_AMT, 0), A.BUSINESSTYPE, A.OCCURTYPE, NVL(A.BILL_COUNT, 1)
FROM DWF.F_AGT_BUSINESS_CONTRACT_H A LEFT JOIN B_M_COM_CUR_TRAN_RATE H1
ON H1.TX_DATE = TO_DATE(:B1 , 'YYYY-MM-DD') AND A.CUR_CD = H1.CUR_CD
AND H1.TRANS_CUR_CD = 'T00RMB2' WHERE A.AGMT_ID = :B2 AND A.CORP_ORG =
'15601' AND A.START_DT <= TO_DATE(:B1 , 'YYYY-MM-DD') AND A.END_DT >
TO_DATE(:B1 , 'YYYY-MM-DD')
Plan hash value: 2249074880
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS OUTER | | 1 | 101 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| F_AGT_BUSINESS_CONTRACT_H | 1 | 71 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | F_AGT_BUSINESS_CONTRACT_H_IDX4 | 1 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| B_M_COM_CUR_TRAN_RATE | 1 | 30 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_B_M_COM_CUR_TRAN_RATE | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."AGMT_ID"=:B2 AND "A"."END_DT">TO_DATE(:B1,'YYYY-MM-DD') AND "A"."CORP_ORG"='15601'
AND "A"."START_DT"<=TO_DATE(:B1,'YYYY-MM-DD'))
filter(("A"."CORP_ORG"='15601' AND "A"."END_DT">TO_DATE(:B1,'YYYY-MM-DD')))
5 - access("H1"."TX_DATE"=TO_DATE(:B1,'YYYY-MM-DD') AND "A"."CUR_CD"="H1"."CUR_CD" AND
"H1"."TRANS_CUR_CD"='T00RMB2')
创建如下索引:
create index dwf.F_AGT_BUSINESS_CONTRACT_H_IDX5 on
dwf.F_AGT_BUSINESS_CONTRACT_H(AGMT_ID, START_DT, END_DT, CORP_ORG, CUST_NO);
Plan hash value: 1136337898
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 198 | 33462 | 21373 (1)| 00:04:17 |
| 1 | HASH GROUP BY | | 198 | 33462 | 21373 (1)| 00:04:17 |
|* 2 | HASH JOIN RIGHT OUTER | | 198 | 33462 | 21372 (1)| 00:04:17 |
| 3 | TABLE ACCESS BY INDEX ROWID | B_M_COM_CUR_TRAN_RATE | 6 | 180 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_B_M_COM_CUR_TRAN_RATE | 6 | | 2 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 184 | 25576 | 21368 (1)| 00:04:17 |
| 6 | NESTED LOOPS | | 849 | 25576 | 21368 (1)| 00:04:17 |
|* 7 | HASH JOIN | | 849 | 65373 | 18820 (1)| 00:03:46 |
| 8 | VIEW | VW_NSO_1 | 1 | 7 | 4 (25)| 00:00:01 |
| 9 | HASH UNIQUE | | 1 | 49 | 4 (25)| 00:00:01 |
|* 10 | FILTER | | | | | |
|* 11 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 12 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 3040 | 3 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | F_AGT_BUSINESS_DUEBILL_H | 129K| 8818K| 18816 (1)| 00:03:46 |
|* 14 | INDEX RANGE SCAN | F_AGT_BUSINESS_CONTRACT_H_IDX5 | 1 | | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | F_AGT_BUSINESS_CONTRACT_H | 1 | 62 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BD"."BUSINESSCURRENCY"="H"."CUR_CD"(+))
4 - access("H"."TX_DATE"(+)=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"H"."TRANS_CUR_CD"(+)='T00RMB2')
filter("H"."TRANS_CUR_CD"(+)='T00RMB2')
7 - access("BD"."HOST_ORG_NO"="ORG_ID")
10 - filter("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2')
11 - access("T"."PARENT_ID"=PRIOR "T"."ID")
filter("T"."ORG_ID"='10000')
13 - filter("BD"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BD"."BALANCE">0 AND
"BD"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - access("BC"."AGMT_ID"="BD"."RELATIVESERIALNO2" AND "BC"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "BC"."CORP_ORG"="BD"."CORP_ORG" AND "BC"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("BC"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BC"."CORP_ORG"="BD"."CORP_ORG")
15 - filter("BC"."BUSINESSTYPE" LIKE '1%')
优化到此结束