----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | | 1112K (1)| 03:42:25 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_MC_CARD_USED | 1 | 20 | | 2 (0)|
|* 3 | INDEX RANGE SCAN | CUSTOMER_USEDCARD_FK | 1 | | | 1 (0)|
| 4 | SORT AGGREGATE | | 1 | 11 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | T_F_PAYMENT_RECORD | 1 | 11 | | 5 (0
|* 6 | INDEX RANGE SCAN | MEMBER_PAYRECORD_FK | 10 | | | 1 (0)|
|* 7 | COUNT STOPKEY | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | T_F_ORDER_PAYMENT_ACTIVE | 1 | 9 | |
|* 9 | INDEX RANGE SCAN | IDX_T_F_ORDER_PAY_AC_C_ID | 2 | | | 1 (0
| 10 | SORT AGGREGATE | | 1 | 20 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | T_F_ORDER_REFUNDMENT | 1 | 20 | | 1
|* 12 | INDEX RANGE SCAN | ORDER_REFUNDMENT_CUSTOMER_FK | 3 | | | 1 (0
|* 13 | FILTER | | | | | | |
|* 14 | HASH JOIN RIGHT ANTI | | 488K| 28M| | 30758 (6)| 00:06:10 |
|* 15 | TABLE ACCESS FULL | T_F_ORDER_REFUNDMENT | 4 | 64 | | 414 (7)
|* 16 | HASH JOIN RIGHT ANTI | | 488K| 21M| | 30327 (6)| 00:06:04 |
|* 17 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO | 361 | 3971 | | 11 (0)|
|* 18 | INDEX RANGE SCAN | HOBINFO_PAYMENTMODE_FK | 669 | | | 1
|* 19 | HASH JOIN RIGHT ANTI | | 489K| 16M| | 30298 (5)| 00:06:04 |
|* 20 | TABLE ACCESS FULL | T_F_ORDER_PAYMENT_ACTIVE | 34646 | 304K| | 115
|* 21 | HASH JOIN ANTI | | 509K| 12M| 19M| 30163 (5)| 00:06:02 |
| 22 | TABLE ACCESS FULL | T_C_CUSTOMER | 752K| 10M| | 3833 (5)| 00
|* 23 | VIEW | index$_join$_007 | 1890K| 19M| | 23136 (5)| 00:04:38 |
|* 24 | HASH JOIN | | | | | | |
|* 25 | HASH JOIN | | | | | | |
|* 26 | INDEX RANGE SCAN | TICKETORDER_CASHSTATE_FK | 1890K| 19M| | 1658 (9)|
| 27 | INLIST ITERATOR | | | | | | |
|* 28 | INDEX RANGE SCAN | ORDER_STATUS_FK | 1890K| 19M| | 4038 (8)| 00:00:4
| 29 | INDEX FAST FULL SCAN | TICKETORDER_MEMBERINFO_FK | 1890K| 19M| | 4695 (3)|
|* 30 | FILTER | | | | | | |
| 31 | SORT AGGREGATE | | 1 | 11 | | | |
|* 32 | TABLE ACCESS BY INDEX ROWID| T_F_PAYMENT_RECORD | 1 | 11 | | 5 (0
|* 33 | INDEX RANGE SCAN | MEMBER_PAYRECORD_FK | 10 | | | 1 (0)
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
3 - access("CUSTOMER_ID"=:B1)
5 - filter("PYMT_STATUS"=2)
6 - access("CUSTOMER_ID"=:B1)
7 - filter(ROWNUM<2)
9 - access("CUSTOMER_ID"=:B1)
11 - filter(("R"."STATUS"=1 OR "R"."STATUS"=2 OR "R"."STATUS"=5) AND
"R"."CREATE_TIME">TO_DATE(TO_CHAR(SYSDATE@!-1,'YYYY-MM-DD'),'YYYY-MM-DD hh24:mi:ss'))
12 - access("R"."CUSTOMER_ID"=:B1)
13 - filter( EXISTS (SELECT /*+ */ 0 FROM "T_F_PAYMENT_RECORD" "T_F_PAYMENT_RECORD" WHERE "CUSTOME
AND "PYMT_STATUS"=2 HAVING SUM("PYMT_LEFT_AMT")>0))
14 - access("T"."CUSTOMER_ID"="C"."CUSTOMER_ID")
15 - filter(("T"."STATUS"=1 OR "T"."STATUS"=2 OR "T"."STATUS"=5) AND
"T"."CREATE_TIME">TO_DATE(TO_CHAR(SYSDATE@!-1,'YYYY-MM-DD'),'YYYY-MM-DD hh24:mi:ss'))
16 - access("OI"."CUSTOMER_ID"="C"."CUSTOMER_ID")
17 - filter("OI"."PAY_STATUS"=2)
18 - access("OI"."PYMT_MEANS_ID"=2)
19 - access("CUSTOMER_ID"="C"."CUSTOMER_ID")
20 - filter("AMOUNT">0)
21 - access("I"."CUSTOMER_ID"="C"."CUSTOMER_ID")
23 - filter("I"."GUEST_PYMT_STS_ID"=1 AND ("I"."ORDER_STATUS_ID"=2 OR "I"."ORDER_STATUS_ID"=3 OR
"I"."ORDER_STATUS_ID"=5 OR "I"."ORDER_STATUS_ID"=6))
24 - access(ROWID=ROWID)
25 - access(ROWID=ROWID)
26 - access("I"."GUEST_PYMT_STS_ID"=1)
28 - access("I"."ORDER_STATUS_ID"=2 OR "I"."ORDER_STATUS_ID"=3 OR "I"."ORDER_STATUS_ID"=5 OR
"I"."ORDER_STATUS_ID"=6)
30 - filter(SUM("PYMT_LEFT_AMT")>0)
32 - filter("PYMT_STATUS"=2)
33 - access("CUSTOMER_ID"=:B1)
已选择74行。
统计信息
----------------------------------------------------------
1137 recursive calls
22 db block gets
1866 consistent gets
41 physical reads
0 redo size
6771 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
19 sorts (memory)
0 sorts (disk)
74 rows processed
------------------------------------------------------------------------------
这条SQL单跑from前面出来80多万行数据,解析时间很快,from前面的那些子查询不存在性
能问题,关键问题就在where后面那些过滤条件,每个Not Exists处理都是大表,且过过滤
的出来的数据很多,我尝试用了下/*+ no_unnest */ 好像没啥效果,没优化思路了,求高
手指教!