select /*+ parallel(d 16)*/ count(*) from t47_transaction_uh d
--174027378
select /*+ parallel(d 16)*/ count(*) from t47_transaction_uh d
where d.TX_DT BETWEEN DATE '2015-10-01' AND DATE
'2017-04-30'
and d.ORGANKEY like '3303%'
and d.OVERAREA_IND = 1
and d.TRANSACTIONKEY LIKE 'DEP%'
--5626
select /*+ parallel(d 16)*/ count(*) from t47_transaction_uh d where d.ORGANKEY like '3303%'
--6135723
select /*+ parallel(d 16)*/ count(*) from t47_transaction_uh d where d.TRANSACTIONKEY LIKE 'DEP%'
--152747335
select /*+ parallel(d 16)*/ count(*) from t47_transaction_uh d where d.ORGANKEY like '3303%' and d.OVERAREA_IND = 1
--7728
select count(*)
from t47_transaction_uh d
where d.TX_DT BETWEEN DATE '2015-10-01' AND DATE
'2017-04-30'
and d.ORGANKEY like '3303%'
and d.OVERAREA_IND = 1
and d.TRANSACTIONKEY LIKE 'DEP%'
--5626
explain plan for select e.PARTY_STATUS_CD 当事人中文名称,
e.PARTY_ENG_NAME 当事人英文名称,
d.ACCT_NUM 账号,
d.PARTY_ID 我行客户号,
e.CARD_NO 开户证件号码,
e.TEL_NO 固定电话,
e.CELL_NO 移动电话,
e.BIRTH_DT 所在地,
f.*
from t47_transaction_uh d
left join t47_party e
on e.party_id = d.party_id
left join t47_agreement f
on f.acct_num = d.acct_num
where d.OVERAREA_IND = '1'
and d.TRANSACTIONKEY LIKE 'DEP%'
and d.TX_DT BETWEEN DATE '2015-10-01' AND DATE
'2017-04-30'
and d.ORGANKEY like '3303%'
Plan hash value: 2902512128
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 488 | 906K (1)| 03:01:20 |
| 1 | NESTED LOOPS OUTER | | 1 | 488 | 906K (1)| 03:01:20 |
| 2 | NESTED LOOPS OUTER | | 1 | 138 | 906K (1)| 03:01:20 |
|* 3 | TABLE ACCESS BY INDEX ROWID | T47_TRANSACTION_UH | 1 | 84 | 906K (1)| 03:01:20 |
|* 4 | INDEX RANGE SCAN | T47_TRANSACTION_UH_IDX1 | 2740K| | 16595 (1)| 00:03:20 |
| 5 | TABLE ACCESS BY INDEX ROWID | T47_PARTY | 1 | 54 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T47_PARTY | 1 | | 1 (0)| 00:00:01 |
| 7 | VIEW | T47_AGREEMENT | 1 | 350 | 8 (0)| 00:00:01 |
| 8 | UNION ALL PUSHED PREDICATE | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T47_CP_DEPOSIT | 1 | 199 | 3 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_T47_CP_DEPOSIT | 1 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| T47_ID_DEPOSIT | 1 | 177 | 4 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_T47_ID_DEPOSIT | 1 | | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| T47_LOAN_ACCT | 1 | 271 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_T47_LOAN_ACCT_A | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."TRANSACTIONKEY" LIKE 'DEP%' AND "D"."TX_DT">=TO_DATE(' 2015-10-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "D"."TX_DT"<=TO_DATE(' 2017-04-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
4 - access("D"."ORGANKEY" LIKE '3303%')
filter("D"."ORGANKEY" LIKE '3303%' AND TO_NUMBER("D"."OVERAREA_IND")=1)
6 - access("E"."PARTY_ID"(+)="D"."PARTY_ID")
10 - access("ACCT_NUM"="D"."ACCT_NUM")
12 - access("ACCT_NUM"="D"."ACCT_NUM")
14 - access("ACCT_NUM"="D"."ACCT_NUM")
-------------------------------------------------------------------------------------------
explain plan for select e.PARTY_STATUS_CD 当事人中文名称,
e.PARTY_ENG_NAME 当事人英文名称,
d.ACCT_NUM 账号,
d.PARTY_ID 我行客户号,
e.CARD_NO 开户证件号码,
e.TEL_NO 固定电话,
e.CELL_NO 移动电话,
e.BIRTH_DT 所在地,
f.*
from t47_transaction_uh d
left join t47_party e
on e.party_id = d.party_id
left join t47_agreement f
on f.acct_num = d.acct_num
where d.OVERAREA_IND = '1'
and d.TRANSACTIONKEY LIKE 'DEP%'
and d.TX_DT BETWEEN DATE '2015-10-01' AND DATE
'2017-04-30'
and d.ORGANKEY like '3303%';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
Plan hash value: 2902512128
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 488 | 550K (1)| 01:50:11 |
| 1 | NESTED LOOPS OUTER | | 1 | 488 | 550K (1)| 01:50:11 |
| 2 | NESTED LOOPS OUTER | | 1 | 138 | 550K (1)| 01:50:11 |
|* 3 | TABLE ACCESS BY INDEX ROWID | T47_TRANSACTION_UH | 1 | 84 | 550K (1)| 01:50:11 |
|* 4 | INDEX RANGE SCAN | T47_TRANSACTION_UH_IDX1 | 1644K| | 16573 (1)| 00:03:19 |
| 5 | TABLE ACCESS BY INDEX ROWID | T47_PARTY | 1 | 54 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T47_PARTY | 1 | | 1 (0)| 00:00:01 |
| 7 | VIEW | T47_AGREEMENT | 1 | 350 | 8 (0)| 00:00:01 |
| 8 | UNION ALL PUSHED PREDICATE | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T47_CP_DEPOSIT | 1 | 199 | 3 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_T47_CP_DEPOSIT | 1 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| T47_ID_DEPOSIT | 1 | 177 | 4 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_T47_ID_DEPOSIT | 1 | | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| T47_LOAN_ACCT | 1 | 271 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_T47_LOAN_ACCT_A | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."TRANSACTIONKEY" LIKE 'DEP%' AND "D"."TX_DT">=TO_DATE(' 2015-10-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "D"."TX_DT"<=TO_DATE(' 2017-04-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
4 - access("D"."ORGANKEY" LIKE '3303%' AND "D"."OVERAREA_IND"='1')
filter("D"."ORGANKEY" LIKE '3303%' AND "D"."OVERAREA_IND"='1')
6 - access("E"."PARTY_ID"(+)="D"."PARTY_ID")
10 - access("ACCT_NUM"="D"."ACCT_NUM")
12 - access("ACCT_NUM"="D"."ACCT_NUM")
14 - access("ACCT_NUM"="D"."ACCT_NUM")