explain plan for select
a.open_org as 机构号,
a.org_name as 机构名,
b.cur_cd as 币种,
sum(b.acct_bal)/10000 as 余额,
count(*) as 户数
from
( select /*+ leading(y) use_nl(x y)*/ x.open_org , x.agmt_id , x.cust_no , y.org_name
from dwf.F_AGT_SAVB_BASICINFO_H x , dwm.B_M_SYS_BRANCH y
where x.start_dt<= to_date('2014-01-01', 'YYYY-MM-DD')
and x.end_dt> to_date('2014-01-01', 'YYYY-MM-DD')
and x.acct_status in('0','S')
and substr(x.cust_no,1,1) in('0','8','9','a')
and x.open_org = y.org_id
and y.parent_id='1015'
)a,
(
select *
from dwf.F_AGT_SAVB_ACCTINFO_H
where substr(subj_cd,1,3) in(201,202,205,251)
and start_dt<= to_date('2014-01-01', 'YYYY-MM-DD')
and end_dt> to_date('2014-01-01', 'YYYY-MM-DD')
)b,
(
select * from dwf.F_PTY_TABLE
where start_dt<= to_date('2014-01-01', 'YYYY-MM-DD')
and end_dt> to_date('2014-01-01', 'YYYY-MM-DD')
and corp_org='15601'
and area_code <>'3310'
)c
where a.agmt_id=b.agmt_id
and a.cust_no=c.pty_id
group by a.open_org,a.org_name,b.cur_cd
;
Plan hash value: 249788832
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1873 | 347K| 58231 (1)| 00:11:39 |
| 1 | HASH GROUP BY | | 1873 | 347K| 58231 (1)| 00:11:39 |
|* 2 | HASH JOIN | | 3056 | 567K| 58230 (1)| 00:11:39 |
| 3 | NESTED LOOPS | | 2981 | 416K| 51040 (1)| 00:10:13 |
| 4 | NESTED LOOPS | | 27214 | 416K| 51040 (1)| 00:10:13 |
|* 5 | HASH JOIN | | 2474 | 215K| 16383 (2)| 00:03:17 |
|* 6 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 5 | 125 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | F_AGT_SAVB_BASICINFO_H | 55736 | 3483K| 16379 (2)| 00:03:17 |
|* 8 | INDEX RANGE SCAN | SYS_C00224616 | 11 | | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| F_AGT_SAVB_ACCTINFO_H | 1 | 54 | 14 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | F_PTY_TABLE | 103K| 4730K| 7189 (2)| 00:01:27 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"."CUST_NO"="F_PTY_TABLE"."PTY_ID")
5 - access("X"."OPEN_ORG"="Y"."ORG_ID")
6 - filter("Y"."PARENT_ID"=1015)
7 - filter(("X"."ACCT_STATUS"='0' OR "X"."ACCT_STATUS"='S') AND "X"."START_DT"<=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND (SUBSTR("X"."CUST_NO",1,1)='0' OR
SUBSTR("X"."CUST_NO",1,1)='8' OR SUBSTR("X"."CUST_NO",1,1)='9' OR SUBSTR("X"."CUST_NO",1,1)='a')
AND "X"."END_DT">TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - access("X"."AGMT_ID"="F_AGT_SAVB_ACCTINFO_H"."AGMT_ID" AND "START_DT"<=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("START_DT"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - filter((TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=201 OR TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=202 OR
TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=205 OR TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=251) AND
"END_DT">TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - filter("CORP_ORG"='15601' AND "START_DT"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "END_DT">TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AREA_CODE"<>'3310')
这里 select count(*) from B_M_SYS_BRANCH Y where ("Y"."PARENT_ID"=1015)
select count(*) from B_M_SYS_BRANCH Y where ("Y"."PARENT_ID"=1015)
--9
这里ID=6 才返回9条记录,没必要走HASH JOIN,只需要B_M_SYS_BRANCH 作为驱动表去驱动F_AGT_SAVB_BASICINFO_H即可
创建如下索引:
create index F_AGT_SAVB_BASICINFO_H_IDX3 on F_AGT_SAVB_BASICINFO_H(OPEN_ORG,ACCT_STATUS,SUBSTR("CUST_NO",1,1))
select
a.open_org as 机构号,
a.org_name as 机构名,
b.cur_cd as 币种,
sum(b.acct_bal)/10000 as 余额,
count(*) as 户数
from
( select /*+ leading(y) use_nl(x y)*/ x.open_org , x.agmt_id , x.cust_no , y.org_name
from dwf.F_AGT_SAVB_BASICINFO_H x , dwm.B_M_SYS_BRANCH y
where x.start_dt<= to_date('2014-01-01', 'YYYY-MM-DD')
and x.end_dt> to_date('2014-01-01', 'YYYY-MM-DD')
and x.acct_status in('0','S')
and substr(x.cust_no,1,1) in('0','8','9','a')
and x.open_org = y.org_id
and y.parent_id='1015'
)a,
(
select *
from dwf.F_AGT_SAVB_ACCTINFO_H
where substr(subj_cd,1,3) in(201,202,205,251)
and start_dt<= to_date('2014-01-01', 'YYYY-MM-DD')
and end_dt> to_date('2014-01-01', 'YYYY-MM-DD')
)b,
(
select * from dwf.F_PTY_TABLE
where start_dt<= to_date('2014-01-01', 'YYYY-MM-DD')
and end_dt> to_date('2014-01-01', 'YYYY-MM-DD')
and corp_org='15601'
and area_code <>'3310'
)c
where a.agmt_id=b.agmt_id
and a.cust_no=c.pty_id
group by a.open_org,a.org_name,b.cur_cd
;
Plan hash value: 1581029793
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 644 | 121K| 28461 (1)| 00:05:42 |
| 1 | HASH GROUP BY | | 644 | 121K| 28461 (1)| 00:05:42 |
| 2 | NESTED LOOPS | | 644 | 121K| 28460 (1)| 00:05:42 |
| 3 | NESTED LOOPS | | 5874 | 121K| 28460 (1)| 00:05:42 |
| 4 | NESTED LOOPS | | 534 | 74226 | 20980 (1)| 00:04:12 |
| 5 | NESTED LOOPS | | 521 | 47932 | 19416 (1)| 00:03:53 |
|* 6 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 5 | 125 | 3 (0)| 00:00:01 |
| 7 | INLIST ITERATOR | | | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| F_AGT_SAVB_BASICINFO_H | 110 | 7370 | 5495 (1)| 00:01:06 |
|* 9 | INDEX RANGE SCAN | F_AGT_SAVB_BASICINFO_H_IDX3 | 13903 | | 49 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | F_PTY_TABLE | 1 | 47 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | SYS_C00224099 | 1 | | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | SYS_C00224616 | 11 | | 3 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | F_AGT_SAVB_ACCTINFO_H | 1 | 54 | 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("Y"."PARENT_ID"=1015)
8 - filter("X"."START_DT"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"X"."END_DT">TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - access("X"."OPEN_ORG"="Y"."ORG_ID" AND ("X"."ACCT_STATUS"='0' OR "X"."ACCT_STATUS"='S') AND
(SUBSTR("CUST_NO",1,1)='0' OR SUBSTR("CUST_NO",1,1)='8' OR SUBSTR("CUST_NO",1,1)='9' OR
SUBSTR("CUST_NO",1,1)='a'))
10 - filter("END_DT">TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AREA_CODE"<>'3310')
11 - access("X"."CUST_NO"="F_PTY_TABLE"."PTY_ID" AND "CORP_ORG"='15601' AND "START_DT"<=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("CORP_ORG"='15601' AND "START_DT"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND SUBSTR("CUST_NO",1,1)=SUBSTR("F_PTY_TABLE"."PTY_ID",1,1))
12 - access("X"."AGMT_ID"="F_AGT_SAVB_ACCTINFO_H"."AGMT_ID" AND "START_DT"<=TO_DATE(' 2014-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("START_DT"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
13 - filter((TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=201 OR TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=202 OR
TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=205 OR TO_NUMBER(SUBSTR("SUBJ_CD",1,3))=251) AND "END_DT">TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))