select
aa.acct_org,
aa.loan_acct_no,
aa.FUNCTIONARY,
aa.cust_no,
sum(dwm.pkg_tools.currcdtran(bb.INTT,aa.trans_cur_cd,'T00CNY','2014-02-10')) as jtlx
from dwf.F_AGT_LONC_BASIC_INFO_H aa
left join dwf.f_agt_comr_intdist_h bb
on aa.loan_acct_no=bb.agmt_id
and bb.dw_data_dt>=to_date('2014-01-01','yyyy-mm-dd')
and bb.dw_data_dt<=to_date('2014-02-10','yyyy-mm-dd')
where aa.start_dt<=to_date('2014-02-10','yyyy-mm-dd')
and aa.end_dt>to_date('2014-02-10','yyyy-mm-dd')
and aa.trans_cur_cd<>'T00CNY'
group by aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no
having sum(bb.payable_int_amt)<>0;
Plan hash value: 2421779894
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1483K| 192M| 3883K (2)| 12:56:39 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1483K| 192M| 3883K (2)| 12:56:39 |
|* 3 | HASH JOIN OUTER | | 1483K| 192M| 3883K (2)| 12:56:38 |
|* 4 | TABLE ACCESS FULL| F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 |
|* 5 | TABLE ACCESS FULL| F_AGT_COMR_INTDIST_H | 53M| 2929M| 3878K (2)| 12:55:42 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("BB"."PAYABLE_INT_AMT")<>0)
3 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+))
4 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "AA"."END_DT">TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "AA"."TRANS_CUR_CD"<>'T00CNY')
5 - filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
create index f_agt_comr_intdist_h_idx2 on f_agt_comr_intdist_h(agmt_id,intt,payable_int_amt,dw_data_dt) tablespace EDWFIDXTBS nologging parallel 8
select
aa.acct_org,
aa.loan_acct_no,
aa.FUNCTIONARY,
aa.cust_no,
sum(dwm.pkg_tools.currcdtran(bb.INTT,aa.trans_cur_cd,'T00CNY','2014-02-10')) as jtlx
from dwf.F_AGT_LONC_BASIC_INFO_H aa
left join dwf.f_agt_comr_intdist_h bb
on aa.loan_acct_no=bb.agmt_id
and bb.dw_data_dt>=to_date('2014-01-01','yyyy-mm-dd')
and bb.dw_data_dt<=to_date('2014-02-10','yyyy-mm-dd')
where aa.start_dt<=to_date('2014-02-10','yyyy-mm-dd')
and aa.end_dt>to_date('2014-02-10','yyyy-mm-dd')
and aa.trans_cur_cd<>'T00CNY'
group by aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no
having sum(bb.payable_int_amt)<>0;
Plan hash value: 2166463325
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1483K| 192M| 155K (1)| 00:31:09 | | | |
| 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1483K| 192M| 155K (1)| 00:31:09 | Q1,02 | P->S | QC (RAND) |
|* 3 | FILTER | | | | | | Q1,02 | PCWC | |
| 4 | HASH GROUP BY | | 1483K| 192M| 155K (1)| 00:31:09 | Q1,02 | PCWP | |
| 5 | PX RECEIVE | | 1483K| 192M| 155K (1)| 00:31:09 | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10001 | 1483K| 192M| 155K (1)| 00:31:09 | Q1,01 | P->P | HASH |
| 7 | HASH GROUP BY | | 1483K| 192M| 155K (1)| 00:31:09 | Q1,01 | PCWP | |
| 8 | NESTED LOOPS OUTER | | 1483K| 192M| 155K (1)| 00:31:08 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 10 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 11 | PX SEND ROUND-ROBIN| :TQ10000 | | | | | | S->P | RND-ROBIN |
|* 12 | TABLE ACCESS FULL | F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 | | | |
|* 13 | INDEX RANGE SCAN | F_AGT_COMR_INTDIST_H_IDX2 | 2412 | 134K| 284 (1)| 00:00:04 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(SUM(SYS_OP_CSR(SYS_OP_MSR(SUM("BB"."PAYABLE_INT_AMT"),SUM("PKG_TOOLS"."CURRCDTRAN"("BB"."INTT","AA"."TRANS_CUR_CD"
,'T00CNY','2014-02-10'))),0))<>0)
12 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."END_DT">TO_DATE(' 2014-02-10
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."TRANS_CUR_CD"<>'T00CNY')
13 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+) AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
alter index f_agt_comr_intdist_h_idx2 noparallel;
关闭并行后:
Plan hash value: 2676048883
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1483K| 192M| 155K (1)| 00:31:09 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1483K| 192M| 155K (1)| 00:31:09 |
| 3 | NESTED LOOPS OUTER| | 1483K| 192M| 155K (1)| 00:31:08 |
|* 4 | TABLE ACCESS FULL| F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 |
|* 5 | INDEX RANGE SCAN | F_AGT_COMR_INTDIST_H_IDX2 | 2412 | 134K| 284 (1)| 00:00:04 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("BB"."PAYABLE_INT_AMT")<>0)
4 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "AA"."END_DT">TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AA"."TRANS_CUR_CD"<>'T00CNY')
5 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+) AND "BB"."DW_DATA_DT"(+)>=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)<=TO_DATE('
2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
大表必须放在被驱动表上,走NL循环.
走Index fast full scan呢？
explain plan for select /*+ index_ffs(bb F_AGT_COMR_INTDIST_H_IDX2)*/
aa.acct_org,
aa.loan_acct_no,
aa.FUNCTIONARY,
aa.cust_no,
sum(dwm.pkg_tools.currcdtran(bb.INTT,aa.trans_cur_cd,'T00CNY','2014-02-10')) as jtlx
from dwf.F_AGT_LONC_BASIC_INFO_H aa
left join dwf.F_AGT_COMR_INTDIST_H bb
on aa.loan_acct_no=bb.agmt_id
and bb.dw_data_dt>=to_date('2014-01-01','yyyy-mm-dd')
and bb.dw_data_dt<=to_date('2014-02-10','yyyy-mm-dd')
where aa.start_dt<=to_date('2014-02-10','yyyy-mm-dd')
and aa.end_dt>to_date('2014-02-10','yyyy-mm-dd')
and aa.trans_cur_cd<>'T00CNY'
group by aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no
having sum(bb.payable_int_amt)<>0;
Plan hash value: 2940667986
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1483K| 192M| 1203K (2)| 04:00:44 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1483K| 192M| 1203K (2)| 04:00:44 |
|* 3 | HASH JOIN OUTER | | 1483K| 192M| 1203K (2)| 04:00:43 |
|* 4 | TABLE ACCESS FULL | F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 |
|* 5 | INDEX FAST FULL SCAN| F_AGT_COMR_INTDIST_H_IDX2 | 53M| 2929M| 1198K (2)| 03:59:47 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("BB"."PAYABLE_INT_AMT")<>0)
3 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+))
4 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AA"."END_DT">TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AA"."TRANS_CUR_CD"<>'T00CNY')
5 - filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)