1、 原语句：
1、 SELECT *
2、 FROM (SELECT "MATERIALREQBILL".FTotalQty "TOTALQTY",
3、 "MATERIALREQBILL".FTotalAmount "TOTALAMOUNT",
...
83、 "SUPPLIER".FNumber "SUPPLIER.NUMBER"
84、 FROMT_IM_MaterialReqBill "MATERIALREQBILL"
85、 LEFTOUTERJOINT_SCM_TransactionType "TRANSACTIONTYPE"
86、 ON"MATERIALREQBILL".FTransactionTypeID ="TRANSACTIONTYPE".FID
...
104、 LEFTOUTERJOIN T_ORG_Storage"STORAGEORGUNIT"
105、 ON"MATERIALREQBILL".FStorageOrgUnitID = "STORAGEORGUNIT".FID
106、 RIGHTOUTERJOINT_IM_MaterialReqBillEntry "ENTRY"
107、 ON"MATERIALREQBILL".FID = "ENTRY".FParentID
108、 LEFTOUTERJOIN T_PM_User"AUDITOR"
109、 ON"MATERIALREQBILL".FAuditorID = "AUDITOR".FID
...
144、 WHERE(("MATERIALREQBILL".FID ='8KqOr1eBTW+NBMyslmLN9VAKt14='AND
145、 ("MATERIALREQBILL".FBaseStatus<>0)) AND
146、 ("MATERIALREQBILL".FBaseStatus<>1))
147、 ORDERBY "NUMBER"ASC,"ENTRY.SEQ" ASC)
148、 WHEREROWNUM <=214748364
2、 这段语句的连接方式中，分录使用的右连接，导致了大部分表的全表扫描。执行计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 3970334368
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id |Operation | Name |Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 11M| 109G| | 26M (1)| 87:46:44 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 11M| 109G| | 26M (1)| 87:46:44 |
|* 3 | SORT ORDER BY STOPKEY | | 11M| 105G| 84G| 26M (1)| 87:46:44 |
|* 4 | HASH JOIN RIGHT OUTER | | 11M| 105G| | 2769K (1)| 09:13:57 |
| 5 | INDEX FAST FULL SCAN |IDX_SUPPLIER_1 | 4772 | 340K| | 24 (0)| 00:00:01 |
| 6 | VIEW | | 11M| 104G| | 2769K (1)| 09:13:56 |
| 7 | NESTED LOOPS OUTER | | 11M| 100G| | 2769K (1)| 09:13:56 |
| 8 | VIEW | | 11M| 99G| | 2769K (1)| 09:13:52 |
|* 9 | HASH JOIN RIGHT OUTER | | 11M| 100G| | 2769K (1)| 09:13:52 |
| 10 | TABLE ACCESS FULL |T_MM_PROJECT | 1 | 146 | | 2 (0)| 00:00:01 |
| 11 | VIEW | | 11M| 98G| | 2769K (1)| 09:13:51 |
|* 12 | HASH JOIN RIGHT OUTER | | 11M| 99G| | 2769K (1)| 09:13:51 |
| 13 | TABLE ACCESS FULL |T_MM_TRACKNUMBER | 1 | 146 | | 2 (0)| 00:00:01 |
| 14 | VIEW | | 11M| 97G| | 2769K (1)| 09:13:51 |
|* 15 | HASH JOIN RIGHT OUTER | | 11M| 98G| | 2769K (1)| 09:13:51 |
| 16 | TABLE ACCESS FULL |T_MM_WORKCENTER | 1 | 530 | | 2 (0)| 00:00:01 |
| 17 | VIEW | | 11M| 92G| | 2769K (1)| 09:13:50 |
|* 18 | HASH JOIN RIGHT OUTER | | 1
最低0.47元/天 解锁文章
339
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
