使用union替换or，并在bms_terMultiApp表createDate增加索引来提高过滤效率
SQL> ;
1* select * from v$version
SQL> /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
Elapsed: 00:00:00.00
SQL>
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.73
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.90
SQL>
SQL>
SQL> set autotrace traceonly
SQL>
SQL>
SQL> SELECT
2 DISTINCT orginfo.Organize_Code,
3 app.cupsflag,
4 app.fixedrate,
5 app.leastmoney,
6 app.mostmoney,
7 app.settlementaccntname,
8 app.settlementaccntno,
9 app.settlement_bank_name,
10 app.settlement_bank_no,
11 apptype.name apptype_name,
12 mer.briefname,
13 app.memberInstMerNo,
14 mer.register_address,
15 merpersion.name merpersion_name,
16 merpersion.tel,
17 mer.id mer_id,
18 app.id app_id,
19 mermcc.code mermcc_code,
20 acquirerdev.code acquirerdev_code,
21 orginfo.Organize_Bm,
22 retCommStd.Name retCommStd_Name
23 FROM bms_mer_multapp app,
24 bms_merchant mer,
25 bms_basearg merstate,
26 bms_memberInst networktransfer,
27 bms_developingside networkdev,
28 bms_memberInst acquirerinst,
29 bms_developingside acquirerdev,
30 bms_basearg apptype,
31 m_organize_info orginfo,
32 bms_terMultiApp terapp,
33 bms_person merpersion,
34 bms_mccCode mermcc,
35 bms_basearg retCommStd,
36 m_organize_info orginfo_org
37 WHERE app.merchant = mer.id
38 AND terapp.mermultiapp = app.id
39 AND terapp.mermultiapp = app.id
40 AND mer.state = merstate.id
41 AND app.networktransferinst = networktransfer.devside_id
42 AND networktransfer.devside_id(+) = networkdev.id
43 AND app.acquirer = acquirerinst.devside_id
44 AND acquirerinst.devside_id(+) = acquirerdev.id
45 AND app.apptype = apptype.id
46 AND app.belong_to_inst = orginfo.organize_code
47 AND app.belong_to_org = orginfo_org.organize_code
48 AND mer.contactperson = merpersion.id
49 AND mer.merchantcategory = mermcc.id
50 AND app.retcommstd = retCommStd.id
51 AND app.memberinstmerno LIKE '898%'
52 AND length(app.mostmoney) <= 10
53 AND length(merpersion.tel) <= 30
54 AND mer.over_area_flag in (0, 1, 3)
55 AND merstate.name = '正常'
56 AND mer.cupsflag = '1'
57 AND networkdev.configcode = '00010000'
58 AND acquirerdev.code like '480%'
59 AND app.fixedrate IS NOT NULL
60 AND app.leastmoney IS NOT NULL
61 AND app.mostmoney IS NOT NULL
62 AND app.settlementaccntno is NOT NULL
63 AND app.settlementaccntname IS NOt NULL
64 AND app.settlement_bank_no IS NOt NULL
65 AND app.settlement_bank_name IS NOt NULL
66 AND length(app.memberInstMerNo) = 15
67 AND app.cupsflag = '1'
68 and app.cupsflag is not null
69 AND apptype.name = '便利支付'
70 and ((app.rec_State is not null AND app.rec_State <> '2' and
71 app.updateDate >= SYSDATE-120 and app.updateDate <= SYSDATE) or
72 (terapp.rec_State is not null AND terapp.rec_State <> '2' and
73 terapp.createDate >= SYSDATE-120 and terapp.createDate <= SYSDATE));
14 rows selected.
Elapsed: 00:21:45.28
Execution Plan
----------------------------------------------------------
Plan hash value: 614392375
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 679 | 21 (5)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 679 | 21 (5)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | BMS_TERMULTIAPP | 1 | 41 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 679 | 20 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 638 | 15 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 596 | 14 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 551 | 12 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 541 | 11 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 390 | 9 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 373 | 8 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 364 | 8 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 346 | 7 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 337 | 7 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 327 | 6 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 285 | 5 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 280 | 5 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID| BMS_MER_MULTAPP | 1 | 238 | 4 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | MULTIMERNO_INDEX | 1 | | 3 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | SYS_C005402 | 1 | 5 | 0 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_M_ORGANIZE_INFO_TEST2 | 1 | 10 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | SYS_C005614 | 1 | 9 | 0 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | IDX_BMS_DEVELOPINGSIDE_TEST | 1 | 18 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | SYS_C005614 | 1 | 9 | 0 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | BMS_DEVELOPINGSIDE | 1 | 17 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | SYS_C005302 | 1 | | 0 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | BMS_MERCHANT | 1 | 151 | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | SYS_C005630 | 1 | | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | BMS_MCCCODE | 1 | 10 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | SYS_C005608 | 1 | | 0 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | BMS_PERSON | 1 | 45 | 2 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | SYS_C005765 | 1 | | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | IDX_TERMULTIAPP_MERMULTIAPP | 5 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("APP"."UPDATEDATE">=SYSDATE@!-120 AND "APP"."UPDATEDATE"<=SYSDATE@! AND "APP"."REC_STATE" IS NOT
NULL AND "APP"."REC_STATE"<>'2' OR "TERAPP"."CREATEDATE">=SYSDATE@!-120 AND "TERAPP"."CREATEDATE"<=SYSDATE@! AND
"TERAPP"."REC_STATE" IS NOT NULL AND "TERAPP"."REC_STATE"<>'2')
16 - filter("APP"."SETTLEMENT_BANK_NO" IS NOT NULL AND "APP"."SETTLEMENT_BANK_NAME" IS NOT NULL AND
"APP"."CUPSFLAG" IS NOT NULL AND "APP"."CUPSFLAG"='1' AND LENGTH(TO_CHAR("APP"."MOSTMONEY"))<=10 AND
"APP"."SETTLEMENTACCNTNO" IS NOT NULL AND "APP"."SETTLEMENTACCNTNAME" IS NOT NULL AND "APP"."FIXEDRATE" IS NOT
NULL AND "APP"."LEASTMONEY" IS NOT NULL AND "APP"."MOSTMONEY" IS NOT NULL)
17 - access("APP"."MEMBERINSTMERNO" LIKE '898%')
filter("APP"."MEMBERINSTMERNO" LIKE '898%' AND LENGTH("APP"."MEMBERINSTMERNO")=15)
18 - access("APP"."APPTYPE"="APPTYPE"."ID" AND "APPTYPE"."NAME"='便利支付')
19 - access("APP"."BELONG_TO_ORG"="ORGINFO_ORG"."ORGANIZE_CODE")
20 - access("APP"."RETCOMMSTD"="RETCOMMSTD"."ID")
21 - access("APP"."BELONG_TO_INST"="ORGINFO"."ORGANIZE_CODE")
22 - access("APP"."ACQUIRER"="ACQUIRERINST"."DEVSIDE_ID")
23 - access("ACQUIRERINST"."DEVSIDE_ID"="ACQUIRERDEV"."ID" AND "ACQUIRERDEV"."CODE" LIKE '480%')
filter("ACQUIRERDEV"."CODE" LIKE '480%')
24 - access("APP"."NETWORKTRANSFERINST"="NETWORKTRANSFER"."DEVSIDE_ID")
25 - filter("NETWORKDEV"."CONFIGCODE"='00010000')
26 - access("NETWORKTRANSFER"."DEVSIDE_ID"="NETWORKDEV"."ID")
27 - filter("MER"."CUPSFLAG"='1' AND ("MER"."OVER_AREA_FLAG"=0 OR "MER"."OVER_AREA_FLAG"=1 OR
"MER"."OVER_AREA_FLAG"=3))
28 - access("APP"."MERCHANT"="MER"."ID")
30 - access("MER"."MERCHANTCATEGORY"="MERMCC"."ID")
31 - filter(LENGTH("MERPERSION"."TEL")<=30)
32 - access("MER"."CONTACTPERSON"="MERPERSION"."ID")
33 - access("MER"."STATE"="MERSTATE"."ID" AND "MERSTATE"."NAME"='正常')
34 - access("TERAPP"."MERMULTIAPP"="APP"."ID")
Statistics
----------------------------------------------------------
22009 recursive calls
0 db block gets
2559196 consistent gets
240417 physical reads
116 redo size
4726 bytes sent via SQL*Net to client
1640 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
223 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL>
SQL> set autotrace traceonly
SQL> set timing on
SQL> SELECT
2 DISTINCT orginfo.Organize_Code,
app.cupsflag,
app.fixedrate,
app.leastmoney,
3 4 5 6 app.mostmoney,
app.settlementaccntname,
7 8 app.settlementaccntno,
app.settlement_bank_name,
9 10 app.settlement_bank_no,
apptype.name apptype_name,
11 12 mer.briefname,
app.memberInstMerNo,
mer.register_address,
merpersion.name merpersion_name,
merpersion.tel,
13 14 15 16 17 mer.id mer_id,
app.id app_id,
mermcc.code mermcc_code,
acquirerdev.code acquirerdev_code,
orginfo.Organize_Bm,
retCommStd.Name retCommStd_Name
18 19 20 21 22 23 FROM bms_mer_multapp app,
bms_merchant mer,
bms_basearg merstate,
bms_memberInst networktransfer,
24 25 26 27 bms_developingside networkdev,
bms_memberInst acquirerinst,
bms_developingside acquirerdev,
bms_basearg apptype,
m_organize_info orginfo,
28 29 30 31 32 bms_terMultiApp terapp,
bms_person merpersion,
bms_mccCode mermcc,
bms_basearg retCommStd,
m_organize_info orginfo_org
33 34 35 36 37 WHERE app.merchant = mer.id
AND terapp.mermultiapp = app.id
AND terapp.mermultiapp = app.id
AND mer.state = merstate.id
AND app.networktransferinst = networktransfer.devside_id
AND networktransfer.devside_id(+) = networkdev.id
AND app.acquirer = acquirerinst.devside_id
AND acquirerinst.devside_id(+) = acquirerdev.id
38 39 40 41 42 43 44 45 AND app.apptype = apptype.id
AND app.belong_to_inst = orginfo.organize_code
AND app.belong_to_org = orginfo_org.organize_code
AND mer.contactperson = merpersion.id
AND mer.merchantcategory = mermcc.id
AND app.retcommstd = retCommStd.id
AND app.memberinstmerno LIKE '898%'
AND length(app.mostmoney) <= 10
AND length(merpersion.tel) <= 30
AND mer.over_area_flag in (0, 1, 3)
AND merstate.name = '正常'
AND mer.cupsflag = '1'
AND networkdev.configcode = '00010000'
46 47 48 49 50 51 52 53 54 55 56 57 58 AND acquirerdev.code like '480%'
AND app.fixedrate IS NOT NULL
AND app.leastmoney IS NOT NULL
AND app.mostmoney IS NOT NULL
AND app.settlementaccntno is NOT NULL
AND app.settlementaccntname IS NOt NULL
AND app.settlement_bank_no IS NOt NULL
AND app.settlement_bank_name IS NOt NULL
AND length(app.memberInstMerNo) = 15
59 60 61 62 63 64 65 66 67 AND app.cupsflag = '1'
and app.cupsflag is not null
AND apptype.name = '便利支付'
and (app.rec_State is not null AND app.rec_State <> '2' and
app.updateDate >= SYSDATE-120 and app.updateDate <= SYSDATE)
union
SELECT
DISTINCT orginfo.Organize_Code,
68 69 70 71 72 73 74 75 app.cupsflag,
app.fixedrate,
app.leastmoney,
app.mostmoney,
app.settlementaccntname,
app.settlementaccntno,
app.settlement_bank_name,
app.settlement_bank_no,
apptype.name apptype_name,
76 77 78 79 80 81 82 83 84 mer.briefname,
app.memberInstMerNo,
mer.register_address,
merpersion.name merpersion_name,
merpersion.tel,
mer.id mer_id,
app.id app_id,
mermcc.code mermcc_code,
acquirerdev.code acquirerdev_code,
orginfo.Organize_Bm,
retCommStd.Name retCommStd_Name
85 86 87 88 89 90 91 92 93 94 95 FROM bms_mer_multapp app,
bms_merchant mer,
bms_basearg merstate,
bms_memberInst networktransfer,
bms_developingside networkdev,
96 97 98 99 100 bms_memberInst acquirerinst,
bms_developingside acquirerdev,
bms_basearg apptype,
m_organize_info orginfo,
bms_terMultiApp terapp,
101 102 103 104 105 bms_person merpersion,
bms_mccCode mermcc,
bms_basearg retCommStd,
106 107 108 m_organize_info orginfo_org
WHERE app.merchant = mer.id
AND terapp.mermultiapp = app.id
AND terapp.mermultiapp = app.id
AND mer.state = merstate.id
AND app.networktransferinst = networktransfer.devside_id
AND networktransfer.devside_id(+) = networkdev.id
AND app.acquirer = acquirerinst.devside_id
AND acquirerinst.devside_id(+) = acquirerdev.id
109 110 111 112 113 114 115 116 117 AND app.apptype = apptype.id
AND app.belong_to_inst = orginfo.organize_code
AND app.belong_to_org = orginfo_org.organize_code
118 119 120 AND mer.contactperson = merpersion.id
AND mer.merchantcategory = mermcc.id
AND app.retcommstd = retCommStd.id
AND app.memberinstmerno LIKE '898%'
AND length(app.mostmoney) <= 10
AND length(merpersion.tel) <= 30
AND mer.over_area_flag in (0, 1, 3)
121 122 123 124 125 126 127 AND merstate.name = '正常'
AND mer.cupsflag = '1'
AND networkdev.configcode = '00010000'
AND acquirerdev.code like '480%'
128 129 130 131 AND app.fixedrate IS NOT NULL
AND app.leastmoney IS NOT NULL
AND app.mostmoney IS NOT NULL
132 133 134 AND app.settlementaccntno is NOT NULL
AND app.settlementaccntname IS NOt NULL
AND app.settlement_bank_no IS NOt NULL
AND app.settlement_bank_name IS NOt NULL
AND length(app.memberInstMerNo) = 15
AND app.cupsflag = '1'
and app.cupsflag is not null
AND apptype.name = '便利支付' and
135 136 137 138 139 140 141 142 (terapp.rec_State is not null AND terapp.rec_State <> '2' and
terapp.createDate >= SYSDATE-120 and terapp.createDate <= SYSDATE);143
14 rows selected.
Elapsed: 00:00:08.88
Execution Plan
----------------------------------------------------------
Plan hash value: 1684214314
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 1341 | 29297 (1)| 00:05:52 |
| 1 | SORT UNIQUE | | 2 | 1341 | 29297 (1)| 00:05:52 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | NESTED LOOPS | | 1 | 670 | 29277 (1)| 00:05:52 |
| 5 | NESTED LOOPS | | 1 | 625 | 29275 (1)| 00:05:52 |
| 6 | NESTED LOOPS | | 1 | 593 | 29273 (1)| 00:05:52 |
| 7 | NESTED LOOPS | | 1 | 551 | 29272 (1)| 00:05:52 |
| 8 | NESTED LOOPS | | 1 | 541 | 29271 (1)| 00:05:52 |
| 9 | NESTED LOOPS | | 1 | 390 | 29269 (1)| 00:05:52 |
| 10 | NESTED LOOPS | | 1 | 373 | 29268 (1)| 00:05:52 |
| 11 | NESTED LOOPS | | 1 | 364 | 29268 (1)| 00:05:52 |
| 12 | NESTED LOOPS | | 1 | 346 | 29267 (1)| 00:05:52 |
| 13 | NESTED LOOPS | | 1 | 337 | 29267 (1)| 00:05:52 |
| 14 | NESTED LOOPS | | 1 | 327 | 29266 (1)| 00:05:52 |
| 15 | NESTED LOOPS | | 1 | 285 | 29265 (1)| 00:05:52 |
| 16 | NESTED LOOPS | | 1 | 280 | 29265 (1)| 00:05:52 |
|* 17 | TABLE ACCESS BY INDEX ROWID| BMS_MER_MULTAPP | 1 | 238 | 29264 (1)| 00:05:52 |
|* 18 | INDEX RANGE SCAN | IDX_BMS_MER_MULTAPP_TEST | 39893 | | 109 (1)| 00:00:02 |
|* 19 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | SYS_C005402 | 1 | 5 | 0 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_M_ORGANIZE_INFO_TEST2 | 1 | 10 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | SYS_C005614 | 1 | 9 | 0 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | IDX_BMS_DEVELOPINGSIDE_TEST | 1 | 18 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | SYS_C005614 | 1 | 9 | 0 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | BMS_DEVELOPINGSIDE | 1 | 17 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | SYS_C005302 | 1 | | 0 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | BMS_MERCHANT | 1 | 151 | 2 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | SYS_C005630 | 1 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | BMS_MCCCODE | 1 | 10 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | SYS_C005608 | 1 | | 0 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | IDX_TERMULTIAPP_MERMULTIAPP | 4 | 128 | 2 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | BMS_PERSON | 1 | 45 | 2 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | SYS_C005765 | 1 | | 1 (0)| 00:00:01 |
|* 36 | FILTER | | | | | |
| 37 | NESTED LOOPS | | 1 | 671 | 18 (0)| 00:00:01 |
| 38 | NESTED LOOPS | | 1 | 629 | 17 (0)| 00:00:01 |
| 39 | NESTED LOOPS | | 1 | 611 | 16 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | 1 | 594 | 15 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 1 | 584 | 14 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | 1 | 539 | 12 (0)| 00:00:01 |
| 43 | NESTED LOOPS | | 1 | 497 | 11 (0)| 00:00:01 |
| 44 | NESTED LOOPS | | 1 | 487 | 10 (0)| 00:00:01 |
| 45 | NESTED LOOPS | | 1 | 482 | 10 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 1 | 473 | 10 (0)| 00:00:01 |
| 47 | NESTED LOOPS | | 1 | 464 | 10 (0)| 00:00:01 |
| 48 | NESTED LOOPS | | 1 | 313 | 8 (0)| 00:00:01 |
| 49 | NESTED LOOPS | | 1 | 271 | 7 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID| BMS_TERMULTIAPP | 1 | 41 | 5 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | IDX_BMS_TERMULTIAPP_TEST2 | 4 | | 3 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID| BMS_MER_MULTAPP | 1 | 230 | 2 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | SYS_C005700 | 1 | | 1 (0)| 00:00:01 |
|* 54 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 55 | TABLE ACCESS BY INDEX ROWID | BMS_MERCHANT | 1 | 151 | 2 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | SYS_C005630 | 1 | | 1 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | SYS_C005614 | 1 | 9 | 0 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | SYS_C005614 | 1 | 9 | 0 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | SYS_C005402 | 1 | 5 | 0 (0)| 00:00:01 |
|* 60 | INDEX RANGE SCAN | IDX_M_ORGANIZE_INFO_TEST2 | 1 | 10 | 1 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
|* 62 | TABLE ACCESS BY INDEX ROWID | BMS_PERSON | 1 | 45 | 2 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | SYS_C005765 | 1 | | 1 (0)| 00:00:01 |
| 64 | TABLE ACCESS BY INDEX ROWID | BMS_MCCCODE | 1 | 10 | 1 (0)| 00:00:01 |
|* 65 | INDEX UNIQUE SCAN | SYS_C005608 | 1 | | 0 (0)| 00:00:01 |
|* 66 | TABLE ACCESS BY INDEX ROWID | BMS_DEVELOPINGSIDE | 1 | 17 | 1 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | SYS_C005302 | 1 | | 0 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | IDX_BMS_DEVELOPINGSIDE_TEST | 1 | 18 | 1 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | IDX_BMS_BASEARG_TEST | 1 | 42 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( "APP"."CUPSFLAG" IS NOT NULL AND "APP"."REC_STATE" IS NOT NULL AND "APP"."CUPSFLAG"='1' AND
"APP"."MEMBERINSTMERNO" LIKE '898%' AND LENGTH("APP"."MEMBERINSTMERNO")=15 AND "APP"."REC_STATE"<>'2' AND
LENGTH(TO_CHAR("APP"."MOSTMONEY"))<=10 AND "APP"."SETTLEMENTACCNTNO" IS NOT NULL AND "APP"."SETTLEMENTACCNTNAME"
IS NOT NULL AND "APP"."FIXEDRATE" IS NOT NULL AND "APP"."LEASTMONEY" IS NOT NULL AND "APP"."MOSTMONEY" IS NOT NULL)
18 - access("APP"."UPDATEDATE">=SYSDATE@!-120 AND "APP"."UPDATEDATE"<=SYSDATE@!)
19 - access("APP"."APPTYPE"="APPTYPE"."ID" AND "APPTYPE"."NAME"='便利支付')
20 - access("APP"."BELONG_TO_ORG"="ORGINFO_ORG"."ORGANIZE_CODE")
21 - access("APP"."RETCOMMSTD"="RETCOMMSTD"."ID")
22 - access("APP"."BELONG_TO_INST"="ORGINFO"."ORGANIZE_CODE")
23 - access("APP"."ACQUIRER"="ACQUIRERINST"."DEVSIDE_ID")
24 - access("ACQUIRERINST"."DEVSIDE_ID"="ACQUIRERDEV"."ID" AND "ACQUIRERDEV"."CODE" LIKE '480%')
filter("ACQUIRERDEV"."CODE" LIKE '480%')
25 - access("APP"."NETWORKTRANSFERINST"="NETWORKTRANSFER"."DEVSIDE_ID")
26 - filter("NETWORKDEV"."CONFIGCODE"='00010000')
27 - access("NETWORKTRANSFER"."DEVSIDE_ID"="NETWORKDEV"."ID")
28 - filter("MER"."CUPSFLAG"='1' AND ("MER"."OVER_AREA_FLAG"=0 OR "MER"."OVER_AREA_FLAG"=1 OR
"MER"."OVER_AREA_FLAG"=3))
29 - access("APP"."MERCHANT"="MER"."ID")
31 - access("MER"."MERCHANTCATEGORY"="MERMCC"."ID")
32 - access("MER"."STATE"="MERSTATE"."ID" AND "MERSTATE"."NAME"='正常')
33 - access("TERAPP"."MERMULTIAPP"="APP"."ID")
34 - filter(LENGTH("MERPERSION"."TEL")<=30)
35 - access("MER"."CONTACTPERSON"="MERPERSION"."ID")
36 - filter('2')
51 - access("TERAPP"."CREATEDATE">=SYSDATE@!-120 AND "TERAPP"."CREATEDATE"<=SYSDATE@!)
52 - filter("APP"."SETTLEMENT_BANK_NO" IS NOT NULL AND "APP"."SETTLEMENT_BANK_NAME" IS NOT NULL AND
"APP"."CUPSFLAG" IS NOT NULL AND "APP"."CUPSFLAG"='1' AND "APP"."MEMBERINSTMERNO" LIKE '898%' AND
LENGTH("APP"."MEMBERINSTMERNO")=15 AND LENGTH(TO_CHAR("APP"."MOSTMONEY"))<=10 AND "APP"."SETTLEMENTACCNTNO" IS NOT
NULL AND "APP"."SETTLEMENTACCNTNAME" IS NOT NULL AND "APP"."FIXEDRATE" IS NOT NULL AND "APP"."LEASTMONEY" IS NOT
NULL AND "APP"."MOSTMONEY" IS NOT NULL)
53 - access("TERAPP"."MERMULTIAPP"="APP"."ID")
54 - access("APP"."APPTYPE"="APPTYPE"."ID" AND "APPTYPE"."NAME"='便利支付')
55 - filter("MER"."CUPSFLAG"='1' AND ("MER"."OVER_AREA_FLAG"=0 OR "MER"."OVER_AREA_FLAG"=1 OR
"MER"."OVER_AREA_FLAG"=3))
56 - access("APP"."MERCHANT"="MER"."ID")
57 - access("APP"."ACQUIRER"="ACQUIRERINST"."DEVSIDE_ID")
58 - access("APP"."NETWORKTRANSFERINST"="NETWORKTRANSFER"."DEVSIDE_ID")
59 - access("APP"."BELONG_TO_ORG"="ORGINFO_ORG"."ORGANIZE_CODE")
60 - access("APP"."BELONG_TO_INST"="ORGINFO"."ORGANIZE_CODE")
61 - access("MER"."STATE"="MERSTATE"."ID" AND "MERSTATE"."NAME"='正常')
62 - filter(LENGTH("MERPERSION"."TEL")<=30)
63 - access("MER"."CONTACTPERSON"="MERPERSION"."ID")
65 - access("MER"."MERCHANTCATEGORY"="MERMCC"."ID")
66 - filter("NETWORKDEV"."CONFIGCODE"='00010000')
67 - access("NETWORKTRANSFER"."DEVSIDE_ID"="NETWORKDEV"."ID")
68 - access("ACQUIRERINST"."DEVSIDE_ID"="ACQUIRERDEV"."ID" AND "ACQUIRERDEV"."CODE" LIKE '480%')
filter("ACQUIRERDEV"."CODE" LIKE '480%')
69 - access("APP"."RETCOMMSTD"="RETCOMMSTD"."ID")
Statistics
----------------------------------------------------------
22069 recursive calls
0 db block gets
9204 consistent gets
610 physical reads
0 redo size
4446 bytes sent via SQL*Net to client
4268 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
224 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15115188/viewspace-742595/ ，如需转载，请注明出处，否则将追究法律责任。
