SELECT A.CARDOFBANK,
A.CARD_NO,
to_char(A.TRANS_DATE, 'YYYY-MM-DD') ,
decode(A.TRANS_CODE, '111019', 0, A.TRANS_AMT),
A.FEE_AMT ,
A.TRANS_CODE ,
A.ABS || (case when A.TRANS_CODE = '111005' and A.dc_flag = '1' then '转出'
when A.TRANS_CODE = '111005' and A.dc_flag = '2' then '转入'
end) ,
E.TRANS_TYPE,
E.TRANS_TYPE_DESC ,
case when A.TRANS_CODE = '111005' and A.dc_flag = '2' then 'B4'
else E.BUSINESS_TYPE
END BUSINESS_TYPE,
E.BUSINESS_TYPE_DESC,
A.SEQ_NO ,
A.AUTH_CODE ,
B.EQUIP_TYPE ,
B.EQUIP_NO ,
D.ORG_ID,
D.BRANCH_ID,
D.PARENT_ID,
D.ind_no,
(SELECT OPEN_ORG
FROM DWF.F_CADC_CUPS_BIN
WHERE SUBSTR(CARD_NO, 1, 6) = CARD_BIN
AND LENGTH(CARD_NO) = CARD_NO_LEN),
to_char(A.trans_time, 'HH24:MI:SS') ,
case when A.ABS = '本行卡查询' then '0'
else A.EXT_SEQNO
end as ,
A.OPP_ACCT_NO ,
A.card_bank ,
A.dc_flag
FROM (
select * from dwm.M_ATM_CDM_LIST_1
UNION ALL
select * from dwm.M_ATM_CDM_LIST_2
union all
SELECT '0' CARDOFBANK,
A.BASE_CARD_NO,
'c111013' as TRANS_CODE,
'卡ATM改密' as ABS,
0 as TRANS_AMT,
A.TRANS_DATE,
A.SEQ_NO,
0 as FEE_AMT,
null as AUTH_CODE,
A.TRANS_OPER_NO,
'gm' as dc_flag,
TRANS_TIME,
null,
null,
decode(SUBSTR(BASE_CARD_NO, 1, 6),
'621028',
'本行卡',
'628250',
'本行卡',
'他行卡') as card_bank,
'' as EQ_NO
FROM DWF.F_EVT_CADJ_SPCJOUR A
where (ABS = '卡ATM改密' OR (TRANS_ATTR IN ('6,', '7') AND
TRANS_ADDR IN ('2', '3')))
and a.set_date <=
TO_DATE('2014-06-30', 'YYYY-MM-DD')
AND a.set_date >=
To_Date('2014-01-01', 'YYYY-MM-DD')
union all
select '0' cardofbank,
A.BASE_ACCT_NO as BASE_CARD_NO,
'600000' as TRANS_CODE,
'电子现金圈存' as ABS,
A.TRANS_AMT,
A.dw_data_dt as TRANS_DATE,
null,
0 as FEE_AMT,
A.AUTH_CODE,
null,
'qc' as dc_flag,
to_date((to_char(A.dw_data_dt, 'YYYY-MM-DD') || ' ' ||
substr(A.TRANS_FTP_TIME, 5, 2) || ':' ||
substr(A.TRANS_FTP_TIME, 7, 2) || ':' ||
substr(A.TRANS_FTP_TIME, 9, 2)),
'YYYY-MM-DD hh24:mi:ss') as TRANS_TIME,
A.SYS_TRANS_NO as EXT_SEQNO,
null,
decode(SUBSTR(A.BASE_ACCT_NO, 1, 6),
'621028',
'本行卡',
'628250',
'本行卡',
'他行卡') as card_bank,
A.TERMINALI as EQ_NO
from dwf.f_Evt_ALOD A
where a.dw_data_dt <=
TO_DATE('2014-06-30', 'YYYY-MM-DD')
AND a.dw_data_dt >=
To_Date('2014-01-01', 'YYYY-MM-DD')
AND A.MER_TYPE = '6011'
union all
select '0' cardofbank,
A.CARD_NO as BASE_CARD_NO,
'600000' as TRANS_CODE,
'电子现金圈存' as ABS,
A.TRANS_AMT,
A.set_date as TRANS_DATE,
A.SEQ_NO,
A.FEE_AMT,
A.AUTH_OPER AS AUTH_CODE,
A.TRANS_OPER_NO,
'qc' as dc_flag,
to_date(to_char(A.set_date, 'YYYY-MM-DD') || ' ' ||
substr(A.TRANS_TIME, 1, 2) || ':' ||
substr(A.TRANS_TIME, 3, 2) || ':' ||
substr(A.TRANS_TIME, 5, 2),
'YYYY-MM-DD hh24:mi:ss') as TRANS_TIME,
null,
A.OPP_ACCT_NO,
'本行卡' as card_bank,
B.EQUIP_NO
from dwf.F_EVT_CADJ_OFF_TRANS_LIST A
inner join dwf.F_CADC_EQUIP B
ON trim(A.TRANS_OPER_NO) = B.OPP_OPER_NO
where trim(A.TRANS_TYPE) in ('1101', '1102')
AND trim(TRANS_CODE) in ('111051')
AND A.SET_DATE <= TO_DATE('2014-06-30', 'YYYY-MM-DD')
and A.SET_DATE >= To_Date('2014-01-01', 'YYYY-MM-DD')
) A
LEFT JOIN
(SELECT A.EQUIP_ATTR,
A.MERCHANT_NO,
A.OPP_OPER_NO,
(CASE
WHEN A.EQUIP_ATTR = '2' THEN
'ATM'
WHEN A.EQUIP_ATTR = '4' THEN
'CDM'
END) EQUIP_TYPE,
A.EQUIP_NO
FROM DWF.F_CADC_EQUIP A) B
ON (A.TRANS_OPER_NO = B.OPP_OPER_NO OR A.EQ_NO = B.EQUIP_NO)
LEFT JOIN (SELECT AGMT_ID, CARD_OPEN_ORG
FROM DWF.F_AGT_CADB_BOOK_H
WHERE START_DT <=
TO_DATE('2014-06-30', 'YYYY-MM-DD')
AND END_DT >
TO_DATE('2014-06-30', 'YYYY-MM-DD')) C
ON A.CARD_NO = C.AGMT_ID
LEFT JOIN (SELECT T1.ORG_ID ORG_ID,
T1.PARENT_ORG_ID BRANCH_ID,
T2.PARENT_ORG_ID PARENT_ID,
T1.IND_NO IND_NO
FROM DWM.B_M_SYS_BRANCH t1, DWM.B_M_SYS_BRANCH t2
WHERE T1.PARENT_ORG_ID = T2.ORG_ID) D
ON B.MERCHANT_NO = D.ORG_ID
LEFT JOIN (SELECT *
FROM dwm.E_BANK_CARD_CHANNEL_CODE
WHERE REPORT_TABLE_ID = 'REPORT_ID_00017') E
ON A.TRANS_CODE = E.TRANS_CODE
where A.TRANS_DATE >= To_Date('2014-01-01', 'YYYY-MM-DD')
and A.TRANS_DATE <= TO_DATE('2014-06-30', 'YYYY-MM-DD')
and A.Abs != 'ATM 脚本通知'
and A.Abs != 'ATM脚本通知'
and A.Abs != '本行卡账户验证'
and D.ORG_ID in
(SELECT t.Org_Id
FROM dwm.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');
1 07-8月 -14 11.40.54.510 上午 953 direct path read 59 1222272 16 0 334101 59 1222256
2 07-8月 -14 11.40.52.510 上午 953 direct path read 73 3168432 16 0 334101 73 3168416
3 07-8月 -14 11.40.50.510 上午 953 direct path read 76 1253264 16 0 334101 76 1253252
4 07-8月 -14 11.40.44.500 上午 953 direct path read 74 422532 12 0 334101 76 1250160
5 07-8月 -14 11.40.28.480 上午 953 direct path read 74 423328 16 0 334101 74 423312
6 07-8月 -14 11.40.22.470 上午 953 direct path read 74 377184 16 0 334101 74 377168
7 07-8月 -14 11.40.06.440 上午 953 direct path read 59 1193024 16 0 334101 59 1193008
8 07-8月 -14 11.39.59.430 上午 953 direct path read 74 402880 16 0 334101 74 402864
9 07-8月 -14 11.39.58.430 上午 953 direct path read 74 430704 16 0 334101 74 430688
10 07-8月 -14 11.39.52.420 上午 953 direct path read 59 1188544 16 0 334101 59 1188528
11 07-8月 -14 11.39.49.410 上午 953 direct path read 76 1199504 16 0 334101 76 1199490
12 07-8月 -14 11.39.44.400 上午 953 direct path read 69 1221408 16 0 334101 69 1221392
13 07-8月 -14 11.39.43.400 上午 953 direct path read 73 3147120 16 0 334101 73 3147104
14 07-8月 -14 11.39.39.390 上午 953 direct path read 69 1221680 16 0 334101 69 1221664
15 07-8月 -14 11.39.04.333 上午 953 direct path read 74 423280 16 0 334101 74 423264
NND 参数被修改成16了
SQL> show parameter multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
1 07-8月 -14 11.47.23.302 上午 953 direct path read 69 1164290 126 0 334101 73 3135618
2 07-8月 -14 11.47.02.262 上午 953 direct path read 76 1222656 128 0 334101 76 1222532
3 07-8月 -14 11.47.01.252 上午 953 direct path read 69 1217280 128 0 334101 69 1217152
4 07-8月 -14 11.46.59.242 上午 953 direct path read 69 1201664 128 0 334101 69 1201540
5 07-8月 -14 11.46.57.242 上午 953 direct path read 76 1257088 128 0 334101 76 1256960
6 07-8月 -14 11.46.51.232 上午 953 direct path read 74 377088 128 0 334101 74 376960
7 07-8月 -14 11.46.41.212 上午 953 direct path read 73 3136898 126 0 334101 74 372994
8 07-8月 -14 11.46.11.152 上午 953 direct path read 69 1168000 128 0 334101 69 1167872
9 07-8月 -14 11.46.06.142 上午 953 direct path read 69 1202432 128 0 334101 69 1202304
10 07-8月 -14 11.45.55.112 上午 953 direct path read 59 1181952 128 0 334101 59 1181837
11 07-8月 -14 11.45.51.112 上午 953 direct path read 74 408196 124 0 334101 76 1243904
12 07-8月 -14 11.45.37.082 上午 953 direct path read 74 423040 128 0 334101 74 422912
13 07-8月 -14 11.45.21.027 上午 953 direct path read 74 408848 16 0 334101 74 408832
14 07-8月 -14 11.45.17.027 上午 953 direct path read 76 1226112 16 0 334101 76 1226096
15 07-8月 -14 11.45.12.007 上午 953 direct path read 59 1188416 16 0 334101 59 1188400
16 07-8月 -14 11.44.58.977 上午 953 direct path read 74 429840 16 0 334101 74 429824
17 07-8月 -14 11.44.55.977 上午 953 direct path read 76 1226496 16 0 334101 76 1226480
18 07-8月 -14 11.44.54.977 上午 953 direct path read 76 1243056 16 0 334101 76 1243040
19 07-8月 -14 11.44.27.917 上午 953 direct path read 74 420416 16 0 334101 74 420400
20 07-8月 -14 11.44.21.907 上午 953 direct path read 74 373200 16 0 334101 74 373184
重复的进行多块读:
Plan hash value: 2114407011
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 621K| 234M| | 224K (1)| 00:44:55 |
|* 1 | INDEX FAST FULL SCAN | SYS_C00224558 | 1 | 20 | | 5 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 621K| 234M| | 224K (1)| 00:44:55 |
| 3 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 2128 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 629K| 229M| | 224K (1)| 00:44:55 |
|* 5 | TABLE ACCESS FULL | E_BANK_CARD_CHANNEL_CODE | 46 | 2024 | | 4 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 629K| 203M| 12M| 224K (1)| 00:44:55 |
| 7 | NESTED LOOPS | | 40962 | 11M| | 188K (1)| 00:37:41 |
|* 8 | HASH JOIN | | 7 | 476 | | 10 (10)| 00:00:01 |
|* 9 | HASH JOIN | | 6 | 270 | | 7 (15)| 00:00:01 |
| 10 | VIEW | VW_NSO_1 | 6 | 162 | | 4 (25)| 00:00:01 |
| 11 | HASH UNIQUE | | 6 | 294 | | 4 (25)| 00:00:01 |
|* 12 | FILTER | | | | | | |
|* 13 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | | |
| 14 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 3040 | | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 2736 | | 3 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | F_CADC_EQUIP | 181 | 4163 | | 3 (0)| 00:00:01 |
|* 17 | VIEW | | 5733 | 1321K| | 26909 (1)| 00:05:23 |
| 18 | UNION-ALL | | | | | | |
|* 19 | TABLE ACCESS FULL | M_ATM_CDM_LIST_1 | 98914 | 8307K| | 4402 (1)| 00:00:53 |
|* 20 | TABLE ACCESS FULL | M_ATM_CDM_LIST_2 | 419K| 38M| | 19373 (1)| 00:03:53 |
|* 21 | TABLE ACCESS FULL | F_EVT_CADJ_SPCJOUR | 1465 | 98155 | | 3123 (2)| 00:00:38 |
|* 22 | TABLE ACCESS FULL | F_EVT_ALOD | 28 | 1988 | | 4 (0)| 00:00:01 |
|* 23 | HASH JOIN | | 1 | 85 | | 8 (0)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | F_EVT_CADJ_OFF_TRANS_LIST | 1 | 70 | | 5 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | F_CADC_EQUIP | 181 | 2715 | | 3 (0)| 00:00:01 |
|* 26 | INDEX FAST FULL SCAN | F_AGT_CADB_BOOK_H_IDX1 | 8553K| 277M| | 16845 (1)| 00:03:23 |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CARD_NO_LEN"=LENGTH(:B1) AND "CARD_BIN"=SUBSTR(:B2,1,6))
2 - access("T1"."PARENT_ORG_ID"=TO_NUMBER("T2"."ORG_ID"))
4 - access("A"."TRANS_CODE"="E_BANK_CARD_CHANNEL_CODE"."TRANS_CODE"(+))
5 - filter("REPORT_TABLE_ID"(+)='REPORT_ID_00017')
6 - access("A"."CARD_NO"="AGMT_ID"(+))
8 - access("A"."MERCHANT_NO"="T1"."ORG_ID")
9 - access("T1"."ORG_ID"="ORG_ID")
12 - filter("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2')
13 - access("T"."PARENT_ID"=PRIOR "T"."ID")
filter("T"."ORG_ID"='10000')
17 - filter("A"."TRANS_OPER_NO"="A"."OPP_OPER_NO" OR "A"."EQ_NO"="A"."EQUIP_NO")
19 - filter("M_ATM_CDM_LIST_1"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M_ATM_CDM_LIST_1"."ABS"<>'本行卡账户验证' AND "M_ATM_CDM_LIST_1"."ABS"<>'ATM脚本通知' AND "M_ATM_CDM_LIST_1"."ABS"<>'ATM 脚本通知' AND
"M_ATM_CDM_LIST_1"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
20 - filter("M_ATM_CDM_LIST_2"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M_ATM_CDM_LIST_2"."ABS"<>'ATM 脚本通知' AND "M_ATM_CDM_LIST_2"."ABS"<>'ATM脚本通知' AND "M_ATM_CDM_LIST_2"."ABS"<>'本行卡账户验证' AND
"M_ATM_CDM_LIST_2"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
21 - filter("A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."TRANS_DATE">=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("TRANS_ATTR"='7' AND ("TRANS_ADDR"='2' OR "TRANS_ADDR"='3') OR
"ABS"='卡ATM改密') AND "A"."SET_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."TRANS_DATE"<=TO_DATE('
2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
22 - filter("A"."DW_DATA_DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."MER_TYPE"='6011' AND
"A"."DW_DATA_DT"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
23 - access("B"."OPP_OPER_NO"=TRIM("A"."TRANS_OPER_NO"))
24 - filter(TRIM("A"."TRANS_CODE")='111051' AND "A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A"."SET_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND (TRIM(TO_CHAR("A"."TRANS_TYPE"))='1101' OR
TRIM(TO_CHAR("A"."TRANS_TYPE"))='1102'))
26 - filter("START_DT"(+)<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DT"(+)>TO_DATE(' 2014-06-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
17 - filter("A"."TRANS_OPER_NO"="A"."OPP_OPER_NO" OR "A"."EQ_NO"="A"."EQUIP_NO")
OR 会影响视图展开:
导致对表M_ATM_CDM_LIST_2 多次全表扫描:
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9d916s3t4d49b, child number 1
-------------------------------------
SELECT A.CARDOFBANK, A.CARD_NO,
to_char(A.TRANS_DATE, 'YYYY-MM-DD') ,
decode(A.TRANS_CODE, '111019', 0, A.TRANS_AMT),
A.FEE_AMT , A.TRANS_CODE , A.ABS || (case
when A.TRANS_CODE = '111005' and A.dc_flag = '1' then '????'
when A.TRANS_CODE = '111005' and A.dc_flag = '2'
then '????' end) ,
E.TRANS_TYPE, E.TRANS_TYPE_DESC , case
when A.TRANS_CODE = '111005' and A.dc_flag = '2' then 'B4'
else E.BUSINESS_TYPE END BUSINESS_TYPE,
E.BUSINESS_TYPE_DESC, A.SEQ_NO ,
A.AUTH_CODE , B.EQUIP_TYPE , B.EQUIP_NO ,
D.ORG_ID, D.BRANCH_ID,
D.PARENT_ID, D.ind_no, (SELECT OPEN_ORG
FROM DWF.F_CADC_CUPS_BIN
Plan hash value: 2114407011
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1442K|00:04:42.76 | 18M| 13M| | | |
|* 1 | INDEX FAST FULL SCAN | SYS_C00224558 | 215K| 1 | 213K|00:05:05.27 | 3872K| 0 | | | |
|* 2 | HASH JOIN | | 1 | 622K| 1442K|00:04:42.76 | 18M| 13M| 1344K| 1344K| 1610K (0)|
| 3 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 152 | 152 |00:00:00.01 | 3 | 0 | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 1 | 630K| 1442K|00:04:41.36 | 18M| 13M| 1185K| 1185K| 1159K (0)|
|* 5 | TABLE ACCESS FULL | E_BANK_CARD_CHANNEL_CODE | 1 | 46 | 8 |00:00:00.01 | 8 | 0 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 630K| 1442K|00:04:39.77 | 18M| 13M| 236M| 8710K| 259M (0)|
| 7 | NESTED LOOPS | | 1 | 41020 | 1442K|00:04:33.73 | 18M| 13M| | | |
|* 8 | HASH JOIN | | 1 | 7 | 155 |00:00:00.01 | 10 | 0 | 1421K| 1421K| 1265K (0)|
|* 9 | HASH JOIN | | 1 | 6 | 102 |00:00:00.01 | 6 | 0 | 2061K| 2061K| 1265K (0)|
| 10 | VIEW | VW_NSO_1 | 1 | 6 | 102 |00:00:00.01 | 3 | 0 | | | |
| 11 | HASH UNIQUE | | 1 | 6 | 102 |00:00:00.01 | 3 | 0 | 1754K| 1754K| 1325K (0)|
|* 12 | FILTER | | 1 | | 102 |00:00:00.01 | 3 | 0 | | | |
|* 13 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | 1 | | 116 |00:00:00.01 | 3 | 0 | | | |
| 14 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 152 | 152 |00:00:00.01 | 3 | 0 | | | |
| 15 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 152 | 152 |00:00:00.01 | 3 | 0 | | | |
| 16 | TABLE ACCESS FULL | F_CADC_EQUIP | 1 | 181 | 181 |00:00:00.01 | 4 | 0 | | | |
|* 17 | VIEW | | 155 | 5741 | 1442K|00:04:33.54 | 18M| 13M| | | |
| 18 | UNION-ALL | | 155 | | 246M|00:03:58.58 | 18M| 13M| | | |
|* 19 | TABLE ACCESS FULL | M_ATM_CDM_LIST_1 | 155 | 99097 | 53M|00:00:28.80 | 3060K| 0 | | | |
|* 20 | TABLE ACCESS FULL | M_ATM_CDM_LIST_2 | 155 | 420K| 193M|00:01:47.87 | 13M| 13M| | | |
|* 21 | TABLE ACCESS FULL | F_EVT_CADJ_SPCJOUR | 155 | 1439 | 0 |00:00:12.32 | 2163K| 0 | | | |
|* 22 | TABLE ACCESS FULL | F_EVT_ALOD | 155 | 28 | 20925 |00:00:00.01 | 1705 | 0 | | | |
|* 23 | HASH JOIN | | 155 | 1 | 5890 |00:00:00.16 | 2945 | 0 | 930K| 930K| 1254K (0)|
|* 24 | TABLE ACCESS FULL | F_EVT_CADJ_OFF_TRANS_LIST | 155 | 1 | 5890 |00:00:00.03 | 2325 | 0 | | | |
| 25 | TABLE ACCESS FULL | F_CADC_EQUIP | 155 | 181 | 28055 |00:00:00.01 | 620 | 0 | | | |
|* 26 | INDEX FAST FULL SCAN | F_AGT_CADB_BOOK_H_IDX1 | 1 | 8553K| 529K|00:00:02.24 | 106K| 26 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CARD_NO_LEN"=LENGTH(:B1) AND "CARD_BIN"=SUBSTR(:B2,1,6)))
2 - access("T1"."PARENT_ORG_ID"=TO_NUMBER("T2"."ORG_ID"))
4 - access("A"."TRANS_CODE"="E_BANK_CARD_CHANNEL_CODE"."TRANS_CODE")
5 - filter("REPORT_TABLE_ID"='REPORT_ID_00017')
6 - access("A"."CARD_NO"="AGMT_ID")
8 - access("A"."MERCHANT_NO"="T1"."ORG_ID")
9 - access("T1"."ORG_ID"="ORG_ID")
12 - filter(("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2'))
13 - access("T"."PARENT_ID"=PRIOR NULL)
filter("T"."ORG_ID"='10000')
17 - filter(("A"."TRANS_OPER_NO"="A"."OPP_OPER_NO" OR "A"."EQ_NO"="A"."EQUIP_NO"))
19 - filter(("M_ATM_CDM_LIST_1"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "M_ATM_CDM_LIST_1"."ABS"<>'ATM????????' AND
"M_ATM_CDM_LIST_1"."ABS"<>'ATM ????????' AND "M_ATM_CDM_LIST_1"."ABS"<>'??????????????' AND "M_ATM_CDM_LIST_1"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
20 - filter(("M_ATM_CDM_LIST_2"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "M_ATM_CDM_LIST_2"."ABS"<>'ATM????????' AND
"M_ATM_CDM_LIST_2"."ABS"<>'ATM ????????' AND "M_ATM_CDM_LIST_2"."ABS"<>'??????????????' AND "M_ATM_CDM_LIST_2"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
21 - filter(("A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND (("TRANS_ATTR"='7' AND INTERNAL_FUNCTION("TRANS_ADDR")) OR "ABS"='??ATM????') AND "A"."SET_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
22 - filter(("A"."DW_DATA_DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."MER_TYPE"='6011' AND "A"."DW_DATA_DT"<=TO_DATE(' 2014-06-30
00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
23 - access("B"."OPP_OPER_NO"=TRIM("A"."TRANS_OPER_NO"))
24 - filter((TRIM("A"."TRANS_CODE")='111051' AND "A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."SET_DATE"<=TO_DATE('
2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND (TRIM(TO_CHAR("A"."TRANS_TYPE"))='1101' OR TRIM(TO_CHAR("A"."TRANS_TYPE"))='1102')))
26 - filter(("START_DT"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DT">TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
82 rows selected.
| 7 | NESTED LOOPS | | 1 | 41020 | 1442K|00:04:33.73 | 18M| 13M| | | |
|* 8 | HASH JOIN | | 1 | 7 | 155 |00:00:00.01 | 10 | 0 | 1421K| 1421K| 1265K (0)|
|* 9 | HASH JOIN | | 1 | 6 | 102 |00:00:00.01 | 6 | 0 | 2061K| 2061K| 1265K (0)|
| 10 | VIEW | VW_NSO_1 | 1 | 6 | 102 |00:00:00.01 | 3 | 0 | | | |
| 11 | HASH UNIQUE | | 1 | 6 | 102 |00:00:00.01 | 3 | 0 | 1754K| 1754K| 1325K (0)|
|* 12 | FILTER | | 1 | | 102 |00:00:00.01 | 3 | 0 | | | |
|* 13 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | 1 | | 116 |00:00:00.01 | 3 | 0 | | | |
| 14 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 152 | 152 |00:00:00.01 | 3 | 0 | | | |
| 15 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 152 | 152 |00:00:00.01 | 3 | 0 | | | |
| 16 | TABLE ACCESS FULL | F_CADC_EQUIP | 1 | 181 | 181 |00:00:00.01 | 4 | 0 | | | |
|* 17 | VIEW
这里 ID=8为驱动表,导致视图17被干了很多次,视图17例包含的表进行了多次全表扫描。
正确应该是 ID=17 视图作为驱动表来驱动ID=8
explain plan for SELECT /*+ leading (A)*/ *
FROM (
select * from dwm.M_ATM_CDM_LIST_1
UNION ALL -- 数据合并
select * from dwm.M_ATM_CDM_LIST_2
--追加 by leidh 追加改密的数据 2012-08-27 start
union all
SELECT '0' CARDOFBANK, -- 卡所属银行
A.BASE_CARD_NO, -- 卡号
'c111013' as TRANS_CODE, -- 交易代码
'卡ATM改密' as ABS, -- 交易代码描述
0 as TRANS_AMT, -- 交易金额
A.TRANS_DATE, -- 交易日期
A.SEQ_NO, -- 交易流水号
0 as FEE_AMT, -- 手续贯金额
null as AUTH_CODE, -- 交易授权号
A.TRANS_OPER_NO, -- 交易柜员号
'gm' as dc_flag, --借贷标志
TRANS_TIME, -- 交易时间
null, -- 外部流水号
null, --对方账号
decode(SUBSTR(BASE_CARD_NO, 1, 6),
'621028',
'本行卡',
'628250',
'本行卡',
'他行卡') as card_bank,
'' as EQ_NO --设备号
FROM DWF.F_EVT_CADJ_SPCJOUR A
where (ABS = '卡ATM改密' OR (TRANS_ATTR IN ('6,', '7') AND
TRANS_ADDR IN ('2', '3')))
and a.set_date <=
TO_DATE('2014-06-30', 'YYYY-MM-DD')
AND a.set_date >=
To_Date('2014-01-01', 'YYYY-MM-DD')
union all
select '0' cardofbank, --卡所属银行
A.BASE_ACCT_NO as BASE_CARD_NO, --卡号
'600000' as TRANS_CODE, --交易类型 自定义为电子现金圈存
'电子现金圈存' as ABS, --交易代码描述
A.TRANS_AMT, --交易金额
A.dw_data_dt as TRANS_DATE, --交易日期
null, --交易流水号
0 as FEE_AMT, --手续费
A.AUTH_CODE, --交易授权号
null, --交易柜员号
'qc' as dc_flag, --借贷标志
to_date((to_char(A.dw_data_dt, 'YYYY-MM-DD') || ' ' ||
substr(A.TRANS_FTP_TIME, 5, 2) || ':' ||
substr(A.TRANS_FTP_TIME, 7, 2) || ':' ||
substr(A.TRANS_FTP_TIME, 9, 2)),
'YYYY-MM-DD hh24:mi:ss') as TRANS_TIME, -- 交易时间
A.SYS_TRANS_NO as EXT_SEQNO, --外部流水号
null, --对方账号
decode(SUBSTR(A.BASE_ACCT_NO, 1, 6),
'621028',
'本行卡',
'628250',
'本行卡',
'他行卡') as card_bank,
A.TERMINALI as EQ_NO --设备号
from dwf.f_Evt_ALOD A
where a.dw_data_dt <=
TO_DATE('2014-06-30', 'YYYY-MM-DD')
AND a.dw_data_dt >=
To_Date('2014-01-01', 'YYYY-MM-DD')
AND A.MER_TYPE = '6011'
union all
select '0' cardofbank, --卡所属银行
A.CARD_NO as BASE_CARD_NO, --卡号
'600000' as TRANS_CODE, --交易类型 自定义为电子现金圈存
'电子现金圈存' as ABS, --交易代码描述
A.TRANS_AMT, --交易金额
A.set_date as TRANS_DATE, --交易日期
A.SEQ_NO, --交易流水号
A.FEE_AMT, -- 手续费
A.AUTH_OPER AS AUTH_CODE, --交易授权号
A.TRANS_OPER_NO, -- 交易柜员号
'qc' as dc_flag, --借贷标志
to_date(to_char(A.set_date, 'YYYY-MM-DD') || ' ' ||
substr(A.TRANS_TIME, 1, 2) || ':' ||
substr(A.TRANS_TIME, 3, 2) || ':' ||
substr(A.TRANS_TIME, 5, 2),
'YYYY-MM-DD hh24:mi:ss') as TRANS_TIME, -- 交易时间
null,
A.OPP_ACCT_NO, --对方账号
'本行卡' as card_bank,
B.EQUIP_NO --设备号
from dwf.F_EVT_CADJ_OFF_TRANS_LIST A
inner join dwf.F_CADC_EQUIP B
ON trim(A.TRANS_OPER_NO) = B.OPP_OPER_NO --虚拟柜员号相同
where trim(A.TRANS_TYPE) in ('1101', '1102') --指定账户圈存,指定账户圈存
AND trim(TRANS_CODE) in ('111051') --本行柜面ATM
AND A.SET_DATE <= TO_DATE('2014-06-30', 'YYYY-MM-DD')
and A.SET_DATE >= To_Date('2014-01-01', 'YYYY-MM-DD')
) A
LEFT JOIN (SELECT A.EQUIP_ATTR, -- 设备类型
A.MERCHANT_NO, -- 设备所属机构
A.OPP_OPER_NO, -- 操作柜员号
(CASE
WHEN A.EQUIP_ATTR = '2' THEN
'ATM'
WHEN A.EQUIP_ATTR = '4' THEN
'CDM'
END) EQUIP_TYPE, -- 设备类型
A.EQUIP_NO -- 设备型号
FROM DWF.F_CADC_EQUIP A) B
ON (A.TRANS_OPER_NO = B.OPP_OPER_NO OR A.EQ_NO = B.EQUIP_NO)
LEFT JOIN (SELECT AGMT_ID, CARD_OPEN_ORG
FROM DWF.F_AGT_CADB_BOOK_H
WHERE START_DT <=
TO_DATE('2014-06-30', 'YYYY-MM-DD')
AND END_DT >
TO_DATE('2014-06-30', 'YYYY-MM-DD')) C
ON A.CARD_NO = C.AGMT_ID
LEFT JOIN (SELECT T1.ORG_ID ORG_ID, -- 支行机构号
T1.PARENT_ORG_ID BRANCH_ID, -- 分行机构号
T2.PARENT_ORG_ID PARENT_ID, -- 总行机构号
T1.IND_NO IND_NO
FROM DWM.B_M_SYS_BRANCH t1, DWM.B_M_SYS_BRANCH t2
WHERE T1.PARENT_ORG_ID = T2.ORG_ID) D
ON B.MERCHANT_NO = D.ORG_ID
LEFT JOIN (SELECT *
FROM dwm.E_BANK_CARD_CHANNEL_CODE
WHERE REPORT_TABLE_ID = 'REPORT_ID_00017') E
ON A.TRANS_CODE = E.TRANS_CODE
where A.TRANS_DATE >= To_Date('2014-01-01', 'YYYY-MM-DD')
and A.TRANS_DATE <= TO_DATE('2014-06-30', 'YYYY-MM-DD')
and A.Abs != 'ATM 脚本通知'
and A.Abs != 'ATM脚本通知'
and A.Abs != '本行卡账户验证'
and D.ORG_ID in
(SELECT t.Org_Id
FROM dwm.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');
select * from table(dbms_xplan.display());
Plan hash value: 2378464717
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72707 | 31M| 193K (2)| 00:38:40 |
| 1 | CONCATENATION | | | | | |
|* 2 | HASH JOIN OUTER | | 69245 | 29M| 99574 (2)| 00:19:55 |
|* 3 | HASH JOIN RIGHT OUTER | | 4504 | 1812K| 27212 (2)| 00:05:27 |
|* 4 | TABLE ACCESS FULL | E_BANK_CARD_CHANNEL_CODE | 46 | 4324 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 4504 | 1398K| 27209 (2)| 00:05:27 |
| 6 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 2128 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 4564 | 1354K| 27206 (2)| 00:05:27 |
| 8 | VIEW | VW_NSO_1 | 1 | 27 | 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 | HASH JOIN | | 520K| 137M| 27198 (2)| 00:05:27 |
| 14 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 2736 | 3 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 520K| 128M| 27192 (2)| 00:05:27 |
| 16 | NESTED LOOPS | | 520K| 128M| 27192 (2)| 00:05:27 |
| 17 | VIEW | | 520K| 117M| 21360 (2)| 00:04:17 |
| 18 | UNION-ALL | | | | | |
|* 19 | TABLE ACCESS FULL | M_ATM_CDM_LIST_1 | 98914 | 8307K| 3495 (2)| 00:00:42 |
|* 20 | TABLE ACCESS FULL | M_ATM_CDM_LIST_2 | 419K| 38M| 15375 (2)| 00:03:05 |
|* 21 | TABLE ACCESS FULL | F_EVT_CADJ_SPCJOUR | 1465 | 98155 | 2480 (2)| 00:00:30 |
|* 22 | TABLE ACCESS FULL | F_EVT_ALOD | 28 | 1988 | 4 (0)| 00:00:01 |
|* 23 | HASH JOIN | | 1 | 85 | 7 (0)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | F_EVT_CADJ_OFF_TRANS_LIST | 1 | 70 | 4 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | F_CADC_EQUIP | 181 | 2715 | 3 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | SYS_C00224556 | 1 | | 0 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | F_CADC_EQUIP | 1 | 23 | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | F_AGT_CADB_BOOK_H | 8553K| 326M| 72305 (2)| 00:14:28 |
|* 29 | HASH JOIN OUTER | | 3462 | 1528K| 93742 (2)| 00:18:45 |
|* 30 | HASH JOIN RIGHT OUTER | | 225 | 92700 | 21380 (2)| 00:04:17 |
|* 31 | TABLE ACCESS FULL | E_BANK_CARD_CHANNEL_CODE | 46 | 4324 | 3 (0)| 00:00:01 |
|* 32 | HASH JOIN | | 225 | 71550 | 21377 (2)| 00:04:17 |
|* 33 | HASH JOIN | | 2 | 164 | 13 (8)| 00:00:01 |
|* 34 | HASH JOIN | | 2 | 136 | 10 (10)| 00:00:01 |
|* 35 | HASH JOIN | | 1 | 45 | 7 (15)| 00:00:01 |
| 36 | VIEW | VW_NSO_1 | 1 | 27 | 4 (25)| 00:00:01 |
| 37 | HASH UNIQUE | | 1 | 49 | 4 (25)| 00:00:01 |
|* 38 | FILTER | | | | | |
|* 39 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 40 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 3040 | 3 (0)| 00:00:01 |
| 41 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 2736 | 3 (0)| 00:00:01 |
| 42 | TABLE ACCESS FULL | F_CADC_EQUIP | 181 | 4163 | 3 (0)| 00:00:01 |
| 43 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 152 | 2128 | 3 (0)| 00:00:01 |
| 44 | VIEW | | 520K| 117M| 21360 (2)| 00:04:17 |
| 45 | UNION-ALL | | | | | |
|* 46 | TABLE ACCESS FULL | M_ATM_CDM_LIST_1 | 98914 | 8307K| 3495 (2)| 00:00:42 |
|* 47 | TABLE ACCESS FULL | M_ATM_CDM_LIST_2 | 419K| 38M| 15375 (2)| 00:03:05 |
|* 48 | TABLE ACCESS FULL | F_EVT_CADJ_SPCJOUR | 1465 | 98155 | 2480 (2)| 00:00:30 |
|* 49 | TABLE ACCESS FULL | F_EVT_ALOD | 28 | 1988 | 4 (0)| 00:00:01 |
|* 50 | HASH JOIN | | 1 | 85 | 7 (0)| 00:00:01 |
|* 51 | TABLE ACCESS FULL | F_EVT_CADJ_OFF_TRANS_LIST | 1 | 70 | 4 (0)| 00:00:01 |
| 52 | TABLE ACCESS FULL | F_CADC_EQUIP | 181 | 2715 | 3 (0)| 00:00:01 |
|* 53 | TABLE ACCESS FULL | F_AGT_CADB_BOOK_H | 8553K| 326M| 72305 (2)| 00:14:28 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."CARD_NO"="AGMT_ID"(+))
3 - access("A"."TRANS_CODE"="E_BANK_CARD_CHANNEL_CODE"."TRANS_CODE"(+))
4 - filter("REPORT_TABLE_ID"(+)='REPORT_ID_00017')
5 - access("T1"."PARENT_ORG_ID"=TO_NUMBER("T2"."ORG_ID"))
7 - access("T1"."ORG_ID"="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 - access("A"."MERCHANT_NO"="T1"."ORG_ID")
19 - filter("M_ATM_CDM_LIST_1"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M_ATM_CDM_LIST_1"."ABS"<>'本行卡账户验证' AND "M_ATM_CDM_LIST_1"."ABS"<>'ATM脚本通知' AND "M_ATM_CDM_LIST_1"."ABS"<>'ATM 脚本通知'
AND "M_ATM_CDM_LIST_1"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
20 - filter("M_ATM_CDM_LIST_2"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M_ATM_CDM_LIST_2"."ABS"<>'ATM 脚本通知' AND "M_ATM_CDM_LIST_2"."ABS"<>'ATM脚本通知' AND "M_ATM_CDM_LIST_2"."ABS"<>'本行卡账户验证'
AND "M_ATM_CDM_LIST_2"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
21 - filter("A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."TRANS_DATE">=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("TRANS_ATTR"='7' AND ("TRANS_ADDR"='2' OR "TRANS_ADDR"='3') OR
"ABS"='卡ATM改密') AND "A"."SET_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
22 - filter("A"."DW_DATA_DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."MER_TYPE"='6011' AND
"A"."DW_DATA_DT"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
23 - access("B"."OPP_OPER_NO"=TRIM("A"."TRANS_OPER_NO"))
24 - filter(TRIM("A"."TRANS_CODE")='111051' AND "A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A"."SET_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
(TRIM(TO_CHAR("A"."TRANS_TYPE"))='1101' OR TRIM(TO_CHAR("A"."TRANS_TYPE"))='1102'))
26 - access("A"."EQ_NO"="A"."EQUIP_NO")
28 - filter("START_DT"(+)<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DT"(+)>TO_DATE('
2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
29 - access("A"."CARD_NO"="AGMT_ID"(+))
30 - access("A"."TRANS_CODE"="E_BANK_CARD_CHANNEL_CODE"."TRANS_CODE"(+))
31 - filter("REPORT_TABLE_ID"(+)='REPORT_ID_00017')
32 - access("A"."TRANS_OPER_NO"="A"."OPP_OPER_NO")
filter(LNNVL("A"."EQ_NO"="A"."EQUIP_NO"))
33 - access("T1"."PARENT_ORG_ID"=TO_NUMBER("T2"."ORG_ID"))
34 - access("A"."MERCHANT_NO"="T1"."ORG_ID")
35 - access("T1"."ORG_ID"="ORG_ID")
38 - filter("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2')
39 - access("T"."PARENT_ID"=PRIOR "T"."ID")
filter("T"."ORG_ID"='10000')
46 - filter("M_ATM_CDM_LIST_1"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M_ATM_CDM_LIST_1"."ABS"<>'本行卡账户验证' AND "M_ATM_CDM_LIST_1"."ABS"<>'ATM脚本通知' AND "M_ATM_CDM_LIST_1"."ABS"<>'ATM 脚本通知'
AND "M_ATM_CDM_LIST_1"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
47 - filter("M_ATM_CDM_LIST_2"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"M_ATM_CDM_LIST_2"."ABS"<>'ATM 脚本通知' AND "M_ATM_CDM_LIST_2"."ABS"<>'ATM脚本通知' AND "M_ATM_CDM_LIST_2"."ABS"<>'本行卡账户验证'
AND "M_ATM_CDM_LIST_2"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
48 - filter("A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."TRANS_DATE">=TO_DATE('
2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("TRANS_ATTR"='7' AND ("TRANS_ADDR"='2' OR "TRANS_ADDR"='3') OR
"ABS"='卡ATM改密') AND "A"."SET_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
49 - filter("A"."DW_DATA_DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."MER_TYPE"='6011' AND
"A"."DW_DATA_DT"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
50 - access("B"."OPP_OPER_NO"=TRIM("A"."TRANS_OPER_NO"))
51 - filter(TRIM("A"."TRANS_CODE")='111051' AND "A"."SET_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A"."SET_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
(TRIM(TO_CHAR("A"."TRANS_TYPE"))='1101' OR TRIM(TO_CHAR("A"."TRANS_TYPE"))='1102'))
53 - filter("START_DT"(+)<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DT"(+)>TO_DATE('
2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))