|
|
SELECT AR.FID RECID, '' RECBILLNO, AR.FALLAMOUNTFOR, '' FCURWRITTENOFFAMOUNTFOR, '' FNRECEIPTAMOUNT,
CASE WHEN (AR.FALLAMOUNTFOR < '') THEN (-'' * ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, '')))) ELSE ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, ''))) END FCHARGEOFFAMOUNT,
PML.FSRCBILLNO FCHARGEOFFBILLNO
FROM T_AR_RECMACTHLOGENTRY PML
INNER JOIN T_AR_RECMacthLog PM ON (PM.FID = PML.FID AND (PM.FISACTIVATION <> '0'))
INNER JOIN T_AR_RECEIVABLE AR ON AR.FID = PML.FTARGETBILLID
WHERE (((((PML.FTARGETFROMID = 'AR_receivable' AND (PML.FSOURCEFROMID <> 'AR_receivable')) AND (PML.FSOURCEFROMID <> 'AR_RECEIVEBILL'))AND (PML.FSOURCEFROMID <> 'AR_REFUNDBILL'))
AND EXISTS (SELECT '' FROM T_AR_RECEIVABLEENTRY ARE
INNER JOIN TMPBBEF6E241F2611E78CED98BE94F FLOW ON Flow.FRECID = ARE.FENTRYID WHERE ARE.FID= AR.FID))
--/*
AND PML.FENTRYID NOT IN (SELECT DISTINCT TARR.FENTRYID FROM
(SELECT TARME.FENTRYID FROM T_AR_MATCKENTRY TARME
INNER JOIN T_AR_MATCK TARM ON (TARM.FID = TARME.FID AND TARME.FMATCHTYPE = '3')) "TEMP"
LEFT OUTER JOIN T_AR_RECMACTHLOGENTRY TARR ON (TARR.FSRCROWID = "TEMP".FENTRYID AND TARR.FSOURCETYPE ='b9b2335770b84a3aa9b09b22767cd7e3')) --*/
)
16 GROUP BY AR.FID, PML.FSRCBILLNO, AR.FALLAMOUNTFOR, AR.FWRITTENOFFSTATUS;
no rows selected
Elapsed: 01:27:17.38
KINGDEEQLJ@e3db>select * from table(dbms_xplan.display_cursor(null,null,'allstats'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0bxs8rjb1u1w0, child number 0
-------------------------------------
SELECT AR.FID RECID, '' RECBILLNO, AR.FALLAMOUNTFOR, ''
FCURWRITTENOFFAMOUNTFOR, '' FNRECEIPTAMOUNT, CASE WHEN
(AR.FALLAMOUNTFOR < '') THEN (-'' *
ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, '')))) ELSE
ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, ''))) END FCHARGEOFFAMOUNT,
PML.FSRCBILLNO FCHARGEOFFBILLNO FROM T_AR_RECMACTHLOGENTRY
PML INNER JOIN T_AR_RECMacthLog PM ON (PM.FID = PML.FID AND
(PM.FISACTIVATION <> '0')) INNER JOIN T_AR_RECEIVABLE AR ON
AR.FID = PML.FTARGETBILLID WHERE (((((PML.FTARGETFROMID =
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'AR_receivable' AND (PML.FSOURCEFROMID <> 'AR_receivable')) AND
(PML.FSOURCEFROMID <> 'AR_RECEIVEBILL'))AND (PML.FSOURCEFROMID <>
'AR_REFUNDBILL')) AND EXISTS (SELECT '' FROM
T_AR_RECEIVABLEENTRY ARE INNER JOIN
TMPBBEF6E241F2611E78CED98BE94F FLOW ON Flow.FRECID = ARE.FENTRYID WHERE
ARE.FID= AR.FID)) --/* AND
PML.FENTRYID NOT IN (SELECT DISTINCT TARR.FENTRYID FROM
Plan hash value: 1056348893
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:15:42.09 | 1328M| 4518 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 0 |00:15:42.09 | 1328M| 4518 | 838K| 838K| 1/0/0|
|* 2 | FILTER | | 1 | | 0 |00:15:42.09 | 1328M| 4518 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.18 | 19212 | 9 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.18 | 19211 | 9 | | | |
|* 5 | HASH JOIN | | 1 | 1 | 1 |00:00:00.18 | 19209 | 2 | 1301K| 1301K| 1/0/0|
| 6 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 13 | 2 | | | |
| 7 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 12 | 1 | | | |
| 8 | VIEW | VW_SQ_1 | 1 | 1 | 1 |00:00:00.01 | 10 | 1 | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 9 | HASH UNIQUE | | 1 | 1 | 1 |00:00:00.01 | 10 | 1 | 2294K| 2294K| 1/0/0|
| 10 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 10 | 1 | | | |
| 11 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 9 | 1 | | | |
| 12 | TABLE ACCESS FULL | TMPBBEF6E241F2611E78CED98BE94F | 1 | 1 | 1 |00:00:00.01 | 7 | 0 | | | |
|* 13 | INDEX UNIQUE SCAN | PK_AR_RECEIVABLEENTRY | 1 | 1 | 1 |00:00:00.01 | 2 | 1 | | | |
| 14 | TABLE ACCESS BY INDEX ROWID| T_AR_RECEIVABLEENTRY | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | | | |
|* 15 | INDEX UNIQUE SCAN | PK_AR_RECEIVABLE | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | T_AR_RECEIVABLE | 1 | 1 | 1 |00:00:00.01 | 1 | 1 | | | |
|* 17 | TABLE ACCESS FULL | T_AR_RECMACTHLOGENTRY | 1 | 55057 | 96676 |00:00:00.14 | 19196 | 0 | | | |
|* 18 | INDEX UNIQUE SCAN | PK_AR_RECMACTHLOG | 1 | 1 | 1 |00:00:00.01 | 2 | 7 | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | T_AR_RECMACTHLOG | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 20 | FILTER | | 1 | | 1 |00:15:41.91 | 1328M| 4509 | | | |
| 21 | NESTED LOOPS OUTER | | 1 | 2 | 62041 |01:27:16.71 | 1328M| 4509 | | | |
| 22 | NESTED LOOPS | | 1 | 1 | 62041 |00:11:08.01 | 137M| 4509 | | | |
| 23 | INDEX FAST FULL SCAN | PK_AR_MATCK | 1 | 48980 | 31021 |00:00:00.09 | 71 | 72 | | | |
|* 24 | TABLE ACCESS FULL | T_AR_MATCKENTRY | 31021 | 1 | 62041 |00:11:07.69 | 137M| 4437 | | | |
|* 25 | TABLE ACCESS FULL | T_AR_RECMACTHLOGENTRY | 62041 | 1 | 62041 |01:16:08.36 | 1190M| 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - filter( IS NULL)
5 - access("AR"."FID"="PML"."FTARGETBILLID")
13 - access("FLOW"."FRECID"="ARE"."FENTRYID")
15 - access("ITEM_1"="AR"."FID")
17 - filter(("PML"."FTARGETFROMID"='AR_receivable' AND "PML"."FSOURCEFROMID"<>'AR_RECEIVEBILL' AND "PML"."FSOURCEFROMID"<>'AR_receivable' AND
"PML"."FSOURCEFROMID"<>'AR_REFUNDBILL'))
18 - access("PM"."FID"="PML"."FID")
19 - filter("PM"."FISACTIVATION"<>'0')
20 - filter(LNNVL("TARR"."FENTRYID"<>:B1))
24 - filter(("TARM"."FID"="TARME"."FID" AND "TARME"."FMATCHTYPE"='3'))
25 - filter(("TARR"."FSRCROWID"="TARME"."FENTRYID" AND "TARR"."FSOURCETYPE"='b9b2335770b84a3aa9b09b22767cd7e3'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 5 Sql Plan Directives used for this statement
73 rows selected. |
|
楼主: ZALBB
IP卡
狗仔卡
楼主
显身卡