explain plan for
with zz as
(select t.br_org_name,
t.sd_org_name,
t.rw_date,
t.cust_no,
t.cust_name,
t.zd_num,
t.status,
t.sett_acct,
t.dr_date,
t.xuhao,
t.shul ,
t.sett_acct as agret
from M_POS_CUST_SETT t
where t.sett_acct not like '621028%'
union all
select t.br_org_name,
t.sd_org_name,
t.rw_date,
t.cust_no,
t.cust_name,
t.zd_num,
t.status,
B.AGMT_ID sett_acct,
t.dr_date,
t.xuhao,
t.shul ,
t.sett_acct as agret
from M_POS_CUST_SETT t
left join DWF.F_AGT_CADB_BOOK_H A
on t.sett_acct = a.agmt_id
and A.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND A.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND A.MASTER_CARD_NO IS NOT NULL
-- AND SUBSTR(A.CARD_NEW_STATUS, 8, 1) <> '6'
LEFT JOIN DWF.F_AGT_CADB_ACCT ee
on ee.START_DT <=
TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND ee.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD')
and A.MASTER_CARD_NO = EE.AGMT_ID
LEFT JOIN DWF.F_AGT_SAVB_ACCTINFO_H b
on b.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND b.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD')
and EE.ACCT_NO = B.AGMT_ID
AND EE.ACCT_SEQNO = B.ACCT_SEQNO
where t.sett_acct like '621028%' )
select o.FST_ORG_NAME,
o.SEC_ORG_NAME,
o.THD_ORG_NAME,
o.FTH_ORG_NAME,
o.ind_no,
s.br_org_no,
t.BR_ORG_NAME,
t.SD_ORG_NAME,
t.RW_DATE,
t.CUST_NO,
t.CUST_NAME,
t.ZD_NUM,
t.STATUS,
t.SETT_ACCT,
t.DR_DATE,
AVG_BAL,
case when s.br_org_no is not null then '正常'
else '异常' end sts,
t.xuhao,
t.shul ,
s.end_acct_bal,
avg_bal/t.shul avg1,
s.end_acct_bal/t.shul avg2
from (
SELECT t.BR_ORG_NAME,
t.SD_ORG_NAME,
t.RW_DATE,
t.CUST_NO,
t.CUST_NAME,
t.ZD_NUM,
t.STATUS,
t.agret SETT_ACCT,
t.DR_DATE,
t.xuhao,
t.shul ,
SUM(ACCT_BAL * DAY_CNT) / max(DAY_COUNT) AVG_BAL,
sum(end_bal) as end_bal
FROM
(SELECT t.BR_ORG_NAME,
t.SD_ORG_NAME,
t.RW_DATE,
t.CUST_NO,
t.CUST_NAME,
t.ZD_NUM,
t.STATUS,
t.SETT_ACCT,
t.DR_DATE,
t.xuhao,
t.shul ,
t.agret,
XX.AGMT_ID,
XX.START_DT,
XX.END_DT,
XX.ACCT_BAL,
case
when XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD') then
XX.ACCT_BAL
else
0
end end_bal,
CASE
when to_date('2014-01-01', 'YYYY-MM-DD') =
to_date('2014-03-31', 'YYYY-MM-DD') then 1
when XX.START_DT < to_date('2014-01-01', 'YYYY-MM-DD') AND
XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD') THEN
to_date('2014-03-31', 'YYYY-MM-DD') -
to_date('2014-01-01', 'YYYY-MM-DD') + 1
WHEN XX.START_DT >= to_date('2014-01-01', 'YYYY-MM-DD') AND
XX.END_DT <= to_date('2014-03-31', 'YYYY-MM-DD') THEN
XX.END_DT - XX.START_DT
WHEN XX.START_DT < to_date('2014-01-01', 'YYYY-MM-DD') THEN
XX.END_DT - to_date('2014-01-01', 'YYYY-MM-DD')
WHEN XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD') THEN
to_date('2014-03-31', 'YYYY-MM-DD') - XX.START_DT + 1
ELSE
0
END AS DAY_CNT,
to_date('2014-03-31', 'YYYY-MM-DD') -
to_date('2014-01-01', 'YYYY-MM-DD') + 1 AS DAY_COUNT
FROM zz t
left join
DWF.F_AGT_SAVB_ACCTINFO_H XX
on t.SETT_ACCT = xx.agmt_id
AND (
(XX.START_DT >= to_date('2014-01-01', 'YYYY-MM-DD') AND
XX.START_DT <= to_date('2014-03-31', 'YYYY-MM-DD'))
or (XX.START_DT < to_date('2014-01-01', 'YYYY-MM-DD') AND
XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD'))
OR (XX.END_DT > to_date('2014-01-01', 'YYYY-MM-DD') AND
XX.END_DT <= to_date('2014-03-31', 'YYYY-MM-DD')))
and XX.CUR_CD = 'T00CNY'
WHERE t.DR_DATE =
(select max(a.dr_date) from m_pos_cust_sett a)) t
GROUP BY t.BR_ORG_NAME,
t.SD_ORG_NAME,
t.RW_DATE,
t.CUST_NO,
t.CUST_NAME,
t.ZD_NUM,
t.STATUS,
t.xuhao,
t.shul ,
t.agret,
t.DR_DATE) t
left join
(
SELECT
t.agret AGMT_ID, max(xx.acct_org) as br_org_no, sum(xx.acct_bal) end_acct_bal
,t.xuhao
FROM zz t
left join
DWF.F_AGT_SAVB_ACCTINFO_H XX
on t.sett_acct = xx.agmt_id
WHERE XX.CUR_CD = 'T00CNY'
and t.dr_date = (select max(a.dr_date) from m_pos_cust_sett a)
AND XX.START_DT <= to_date('2014-03-31', 'YYYY-MM-DD')
AND XX.End_Dt > to_date('2014-03-31', 'YYYY-MM-DD')
group by t.agret ,t.xuhao
) s
on t.sett_acct = s.AGMT_ID
and t.xuhao = s.xuhao
left join V_M_ORG_LEVEL o
on s.br_org_no = o.org_id
where (s.br_org_no is null or s.br_org_no in (SELECT bb.org_id
FROM b_m_sys_branch bb
WHERE bb.status = '1'
AND bb.dept_flag NOT IN ('2')
CONNECT BY PRIOR bb.id = bb.parent_id
START WITH bb.org_id = 10000)
)
;
select * from table(dbms_xplan.display());
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0pszsnw1v97nm, child number 1
-------------------------------------
with zz as (select t.br_org_name,
t.sd_org_name, t.rw_date,
t.cust_no,
t.cust_name, t.zd_num,
t.status,
t.sett_acct, t.dr_date,
t.xuhao, t.shul ,
t.sett_acct as agret
from M_POS_CUST_SETT t where
t.sett_acct not like '621028%' union all
select t.br_org_name,
t.sd_org_name, t.rw_date,
t.cust_no,
t.cust_name, t.zd_num,
t.status,
Plan hash value: 4217052783
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5074 |00:00:17.11 | 1577K| 575K| 105 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 5074 |00:00:17.11 | 1577K| 575K| 105 | | | |
| 2 | LOAD AS SELECT | | 1 | | 0 |00:00:04.60 | 353K| 15242 | 105 | 530K| 530K| 530K (0)|
| 3 | UNION-ALL | | 1 | | 5400 |00:00:04.59 | 353K| 15242 | 0 | | | |
|* 4 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 2797 | 2781 |00:00:00.01 | 91 | 0 | 0 | | | |
| 5 | VIEW | | 1 | 38905 | 2619 |00:00:04.58 | 353K| 15242 | 0 | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 38905 | 2619 |00:00:04.58 | 353K| 15242 | 0 | | | |
| 7 | VIEW | | 1 | 12736 | 2619 |00:00:03.14 | 91703 | 15242 | 0 | | | |
|* 8 | HASH JOIN OUTER | | 1 | 12736 | 2619 |00:00:03.13 | 91703 | 15242 | 0 | 1073K| 1073K| 1396K (0)|
| 9 | VIEW | | 1 | 2312 | 2293 |00:00:02.55 | 76457 | 0 | 0 | | | |
|* 10 | HASH JOIN RIGHT OUTER | | 1 | 2312 | 2293 |00:00:02.55 | 76457 | 0 | 0 | 37M| 4938K| 38M (0)|
|* 11 | INDEX RANGE SCAN | F_AGT_CADB_BOOK_H_IDX4 | 1 | 2 | 333K|00:00:02.21 | 76366 | 0 | 0 | | | |
|* 12 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 2312 | 2293 |00:00:00.01 | 91 | 0 | 0 | | | |
|* 13 | TABLE ACCESS FULL | F_AGT_CADB_ACCT | 1 | 827K| 605K|00:00:00.28 | 15246 | 15242 | 0 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | F_AGT_SAVB_ACCTINFO_H | 2619 | 3 | 2460 |00:00:01.44 | 261K| 0 | 0 | | | |
|* 15 | INDEX RANGE SCAN | F_AGT_SAVB_ACCTINFO_H_IDX2 | 2619 | 1 | 253K|00:00:00.16 | 8106 | 0 | 0 | | | |
|* 16 | FILTER | | 1 | | 5074 |00:00:12.50 | 1223K| 560K| 0 | | | |
| 17 | NESTED LOOPS OUTER | | 1 | 1 | 5074 |00:00:12.44 | 1222K| 560K| 0 | | | |
|* 18 | HASH JOIN OUTER | | 1 | 1 | 5074 |00:00:12.31 | 1217K| 560K| 0 | 1538K| 1070K| 1437K (0)|
| 19 | VIEW | | 1 | 1 | 5074 |00:00:04.16 | 657K| 105 | 0 | | | |
| 20 | HASH GROUP BY | | 1 | 1 | 5074 |00:00:04.15 | 657K| 105 | 0 | 24M| 4065K| 1492K (0)|
| 21 | NESTED LOOPS OUTER | | 1 | 250K| 94941 |00:00:03.86 | 657K| 105 | 0 | | | |
|* 22 | VIEW | | 1 | 41702 | 5400 |00:00:00.03 | 200 | 105 | 0 | | | |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6F46_6CB3C16D | 1 | 41702 | 5400 |00:00:00.02 | 109 | 105 | 0 | | | |
| 24 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 91 | 0 | 0 | | | |
| 25 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 5109 | 5074 |00:00:00.01 | 91 | 0 | 0 | | | |
| 26 | VIEW | | 5400 | 6 | 94736 |00:00:03.80 | 656K| 0 | 0 | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID | F_AGT_SAVB_ACCTINFO_H | 5400 | 6 | 94736 |00:00:03.76 | 656K| 0 | 0 | | | |
|* 28 | INDEX RANGE SCAN | SYS_C0054556 | 5400 | 19 | 637K|00:00:00.41 | 18975 | 0 | 0 | | | |
| 29 | VIEW | | 1 | 1 | 4872 |00:00:08.13 | 560K| 560K| 0 | | | |
| 30 | HASH GROUP BY | | 1 | 1 | 4872 |00:00:08.13 | 560K| 560K| 0 | 1259K| 1259K| 6786K (0)|
|* 31 | HASH JOIN | | 1 | 126K| 5195 |00:00:08.11 | 560K| 560K| 0 | 1291K| 1291K| 2793K (0)|
|* 32 | VIEW | | 1 | 41702 | 5400 |00:00:00.01 | 197 | 0 | 0 | | | |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6F46_6CB3C16D | 1 | 41702 | 5400 |00:00:00.01 | 106 | 0 | 0 | | | |
| 34 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 91 | 0 | 0 | | | |
| 35 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 5109 | 5074 |00:00:00.01 | 91 | 0 | 0 | | | |
|* 36 | TABLE ACCESS FULL | F_AGT_SAVB_ACCTINFO_H | 1 | 2301K| 1287K|00:00:07.44 | 560K| 560K| 0 | | | |
| 37 | VIEW PUSHED PREDICATE | V_M_ORG_LEVEL | 5074 | 1 | 4872 |00:00:00.12 | 5085 | 0 | 0 | | | |
| 38 | NESTED LOOPS OUTER | | 5074 | 1 | 4872 |00:00:00.11 | 5085 | 0 | 0 | | | |
| 39 | NESTED LOOPS OUTER | | 5074 | 1 | 4872 |00:00:00.09 | 4440 | 0 | 0 | | | |
| 40 | NESTED LOOPS OUTER | | 5074 | 1 | 4872 |00:00:00.06 | 3462 | 0 | 0 | | | |
|* 41 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 5074 | 1 | 4872 |00:00:00.04 | 2270 | 0 | 0 | | | |
|* 42 | INDEX RANGE SCAN | IND_BRANCH_001 | 5074 | 1 | 4872 |00:00:00.02 | 652 | 0 | 0 | | | |
| 43 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 4872 | 1 | 4872 |00:00:00.02 | 1192 | 0 | 0 | | | |
|* 44 | INDEX RANGE SCAN | IND_BRANCH_002 | 4872 | 1 | 4872 |00:00:00.01 | 652 | 0 | 0 | | | |
| 45 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 4872 | 1 | 4872 |00:00:00.02 | 978 | 0 | 0 | | | |
|* 46 | INDEX RANGE SCAN | IND_BRANCH_002 | 4872 | 1 | 4872 |00:00:00.01 | 652 | 0 | 0 | | | |
| 47 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 4872 | 1 | 594 |00:00:00.01 | 645 | 0 | 0 | | | |
|* 48 | INDEX RANGE SCAN | IND_BRANCH_002 | 4872 | 1 | 594 |00:00:00.01 | 377 | 0 | 0 | | | |
|* 49 | FILTER | | 143 | | 143 |00:00:00.05 | 858 | 0 | 0 | | | |
|* 50 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | 143 | | 7094 |00:00:00.05 | 858 | 0 | 0 | | | |
| 51 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 143 | 152 | 21879 |00:00:00.01 | 858 | 0 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T"."SETT_ACCT" NOT LIKE '621028%')
8 - access("A"."MASTER_CARD_NO"="EE"."AGMT_ID")
10 - access("T"."SETT_ACCT"="A"."AGMT_ID")
11 - access("A"."AGMT_ID" LIKE '621028%' AND "A"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
filter(("A"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."AGMT_ID" LIKE '621028%' AND "A"."MASTER_CARD_NO" IS NOT NULL AND
"A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
12 - filter("T"."SETT_ACCT" LIKE '621028%')
13 - filter(("EE"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "EE"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
14 - filter(("B"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
15 - access("EE"."ACCT_NO"="B"."AGMT_ID" AND "EE"."ACCT_SEQNO"="B"."ACCT_SEQNO")
16 - filter(("S"."BR_ORG_NO" IS NULL OR IS NOT NULL))
18 - access("T"."XUHAO"="S"."XUHAO" AND "T"."SETT_ACCT"="S"."AGMT_ID")
22 - filter("T"."DR_DATE"=)
27 - filter(((("XX"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."START_DT"<TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
OR ("XX"."START_DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
("XX"."END_DT">TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."END_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) AND
"XX"."CUR_CD"='T00CNY'))
28 - access("T"."SETT_ACCT"="XX"."AGMT_ID")
31 - access("T"."SETT_ACCT"="XX"."AGMT_ID")
32 - filter("T"."DR_DATE"=)
36 - filter(("XX"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"XX"."CUR_CD"='T00CNY'))
41 - filter((TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2'))
42 - access("A"."ORG_ID"="S"."BR_ORG_NO")
44 - access("A"."PARENT_ID"="B"."ID")
46 - access("B"."PARENT_ID"="C"."ID")
48 - access("C"."PARENT_ID"="D"."ID")
49 - filter(("BB"."ORG_ID"=:B1 AND "BB"."STATUS"=1 AND "BB"."DEPT_FLAG"<>'2'))
50 - access("BB"."PARENT_ID"=PRIOR NULL)
filter(TO_NUMBER("BB"."ORG_ID")=10000)
112 rows selected.
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6F41_6CB3C16D | 1 | 41702 | 5400 |00:00:00.03 | 109 | 105 | 0 | | | |
| 23 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 91 | 0 | 0 | | | |
| 24 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 5109 | 5074 |00:00:00.01 | 91 | 0 | 0 | | | |
这里的5400行是怎么计算的呢？
with A as (select /*+ materialize */ * from (select t.br_org_name,
t.sd_org_name,
t.rw_date,
t.cust_no,
t.cust_name,
t.zd_num,
t.status,
t.sett_acct,
t.dr_date,
t.xuhao,
t.shul ,
t.sett_acct as agret
from M_POS_CUST_SETT t
where t.sett_acct not like '621028%'
union all
select /*+ use_hash(EE B) */ t.br_org_name,
t.sd_org_name,
t.rw_date,
t.cust_no,
t.cust_name,
t.zd_num,
t.status,
B.AGMT_ID sett_acct,
t.dr_date,
t.xuhao,
t.shul ,
t.sett_acct as agret
from M_POS_CUST_SETT t
left join DWF.F_AGT_CADB_BOOK_H A
on t.sett_acct = a.agmt_id
and A.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND A.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND A.MASTER_CARD_NO IS NOT NULL
-- AND SUBSTR(A.CARD_NEW_STATUS, 8, 1) <> '6'
LEFT JOIN DWF.F_AGT_CADB_ACCT ee
on ee.START_DT <=
TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND ee.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD')
and A.MASTER_CARD_NO = EE.AGMT_ID
LEFT JOIN DWF.F_AGT_SAVB_ACCTINFO_H b
on b.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD')
AND b.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD')
and EE.ACCT_NO = B.AGMT_ID
AND EE.ACCT_SEQNO = B.ACCT_SEQNO
where t.sett_acct like '621028%' ))
select count(*) from A
where A.DR_DATE =
(select max(a.dr_date) from m_pos_cust_sett a)
--5400