1. PL/SQL Developer的Explain Plan Window中執行計劃顯示不準確。
可能是SQL語句比較複雜，加了Hints，做了某些修改之後重新執行，PL/SQL只是將之前快取的執行計劃調了出來。如果發現其顯示的執行計劃不準確，可以新開一個Explain Plan Window重新執行，這樣顯示的執行計劃是準確的。
2. 某些情況下Oracle選擇索引有問題。
發現這種情況後通過Hint指定索引。
3. 有時Oracle對Join方法的選擇不準確。
Oracle特別喜歡使用NESTED LOOPS，這種Join方法有其適用的前提條件，例如資料量很大、NESTED LOOPS迴圈次數很多、沒有高效的索引等，都會導致NESTED LOOPS效率急劇下降。因此適當的使用MERGE JOIN、HASH JOIN對效率的提升將相當明顯。
使用Hint指定Join方法。
遠端登入到專案的正式環境測試，初步優化調整之後，語句的執行時間為10秒鐘。
另外老觀點：應用中不要用複雜的SQL，從業務分析設計、表結構設計上避免。
優化前的SQL:
Select MRDemand.TranCode,MRDemand.CompanyCode,MRDemand.PlantCode,
MRDemand.ItemCode,MRDemand.DemandDate,MRDemand.VendorCode,MRDemand.DemandQty,
CASE WHEN (MRDemand.AdjustQty-NVL(ViewTow.SHIPQTY,0))<0 THEN 0
ELSE (MRDemand.AdjustQty-NVL(ViewTow.SHIPQTY,0)) END AdjustQty,
MRDemand.PreDemandQty,MRDemand.PreAdjustQty,
MRDemand.MRType,MRDemand.PlanUGCode,MRDemand.PurchUGCode,MRDemand.OverDueQty,
MRDemand.CreateUser,MRDemand.CreateDate,MRDemand.CreateTime,
MRDemand.LogUser,MRDemand.LogDate,MRDemand.LogTime,
MRDemand.Addition1,MRDemand.Addition2
From MRDemand
Inner Join (
Select PI.CompanyCode,PI.PlantCode,PI.ItemCode
From Plant2Item PI,ChianTypeDef CTD
Where PI.CompanyCode=CTD.CompanyCode And PI.PlantCode=CTD.PlantCode
And PI.ChianSubType=CTD.ChianSubType And CTD.CHIANTYPE<>'Indirect'
) ViewOne
On MRDemand.CompanyCode=ViewOne.CompanyCode And MRDemand.PlantCode=ViewOne.PlantCode
And MRDemand.ItemCode=ViewOne.ItemCode
Inner Join (
Select Distinct a.CompanyCode,a.PlantCode,a.ItemCode
From (
Select Distinct CompanyCode, PlantCode, ItemCode
From MRDemand
Where DemandDate>=to_number(to_char(Sysdate,'YYYYMMDD')) And AdjustQty>0
And VendorCode='*' And PlantCode In ('','2000')) d
Inner Join PlantItem2Vendor a On d.CompanyCode=a.CompanyCode
And d.PlantCode=a.PlantCode And d.ItemCode=a.ItemCode
Inner Join PlantItemVAssign b On a.CompanyCode=b.CompanyCode
And a.PlantCode=b.PlantCode And a.ItemCode=b.ItemCode
Inner Join PlantItemVAssignDetail c On c.AssignCode=b.AssignCode And c.VendorCode=a.VendorCode
Where b.InvalidDate>=to_number(to_char(Sysdate,'YYYYMMDD'))
) View3 On MRDemand.CompanyCode=View3.CompanyCode And MRDemand.PlantCode=View3.PlantCode
And MRDemand.ItemCode=View3.ItemCode
Left Join (
Select ASN.COMPANYCODE,ASN.PLANTCODE,ASNDetail.ITEMCODE,20070321 PLANDATE,
Sum(ASNDetail.SHIPQTY) SHIPQTY
From ASN,ASNDetail
Where ASN.STNo=ASNDetail.STNo And ASN.STTYPE='JIT'
And ASN.STStatus In ('Release','WaitCheck')
And ASNDetail.STDSTATUS In ('Release','WaitCheck')
And ASNDetail.CHECKSTATUS Not In ('Qualified','UnQualified')
And ASNDetail.SSDate>19010101 And ASNDetail.SSDate<20070321
Group By ASN.COMPANYCODE,ASN.PLANTCODE,ASNDetail.ITEMCODE
) ViewTow
On MRDemand.CompanyCode=ViewTow.CompanyCode And MRDemand.PlantCode=ViewTow.PlantCode
And MRDemand.ItemCode=ViewTow.ItemCode And MRDemand.DemandDate=ViewTow.PLANDATE
Where MRDemand.AdjustQty>0 And MRDemand.VendorCode='*'
And MRDemand.DemandDate Between :V00001 And :V00002
and MRDemand.PlantCode in ('','2000')
Union
Select MRDemand.CompanyCode,MRDemand.PlantCode,MRDemand.ItemCode,MRDemand.DemandDate,
MRDemand.VendorCode,MRDemand.TranCode,
MRDemand.DemandQty,MRDemand.AdjustQty,MRDemand.PreDemandQty,MRDemand.PreAdjustQty,
MRDemand.MRType,MRDemand.PlanUGCode,
MRDemand.PurchUGCode,MRDemand.OverDueQty,
MRDemand.CreateUser,MRDemand.CreateDate,MRDemand.CreateTime,
MRDemand.LogUser, MRDemand.LogDate,MRDemand.LogTime,MRDemand.Addition1,MRDemand.Addition2
From MRDemand
Inner Join (
Select PI.CompanyCode,PI.PlantCode,PI.ItemCode
From Plant2Item PI,ChianTypeDef CTD
Where PI.CompanyCode=CTD.CompanyCode And PI.PlantCode=CTD.PlantCode
And PI.ChianSubType=CTD.ChianSubType And CTD.CHIANTYPE<>'Indirect'
) ViewOne
On MRDemand.CompanyCode=ViewOne.CompanyCode And MRDemand.PlantCode=ViewOne.PlantCode
And MRDemand.ItemCode=ViewOne.ItemCode
Inner Join PlantItem2Vendor On MRDemand.CompanyCode=PlantItem2Vendor.CompanyCode
And MRDemand.PlantCode=PlantItem2Vendor.PlantCode And MRDemand.ItemCode=PlantItem2Vendor.ItemCode
And MRDemand.VendorCode=PlantItem2Vendor.VendorCode
Where MRDemand.AdjustQty>0 And MRDemand.VendorCode<>'*' And MRDemand.DemandDate Between :V00003
And :V00004 and MRDemand.PlantCode in ('','2000')
優化前執行計劃:MRDemand.ItemCode,MRDemand.DemandDate,MRDemand.VendorCode,MRDemand.DemandQty,
CASE WHEN (MRDemand.AdjustQty-NVL(ViewTow.SHIPQTY,0))<0 THEN 0
ELSE (MRDemand.AdjustQty-NVL(ViewTow.SHIPQTY,0)) END AdjustQty,
MRDemand.PreDemandQty,MRDemand.PreAdjustQty,
MRDemand.MRType,MRDemand.PlanUGCode,MRDemand.PurchUGCode,MRDemand.OverDueQty,
MRDemand.CreateUser,MRDemand.CreateDate,MRDemand.CreateTime,
MRDemand.LogUser,MRDemand.LogDate,MRDemand.LogTime,
MRDemand.Addition1,MRDemand.Addition2
From MRDemand
Inner Join (
Select PI.CompanyCode,PI.PlantCode,PI.ItemCode
From Plant2Item PI,ChianTypeDef CTD
Where PI.CompanyCode=CTD.CompanyCode And PI.PlantCode=CTD.PlantCode
And PI.ChianSubType=CTD.ChianSubType And CTD.CHIANTYPE<>'Indirect'
) ViewOne
On MRDemand.CompanyCode=ViewOne.CompanyCode And MRDemand.PlantCode=ViewOne.PlantCode
And MRDemand.ItemCode=ViewOne.ItemCode
Inner Join (
Select Distinct a.CompanyCode,a.PlantCode,a.ItemCode
From (
Select Distinct CompanyCode, PlantCode, ItemCode
From MRDemand
Where DemandDate>=to_number(to_char(Sysdate,'YYYYMMDD')) And AdjustQty>0
And VendorCode='*' And PlantCode In ('','2000')) d
Inner Join PlantItem2Vendor a On d.CompanyCode=a.CompanyCode
And d.PlantCode=a.PlantCode And d.ItemCode=a.ItemCode
Inner Join PlantItemVAssign b On a.CompanyCode=b.CompanyCode
And a.PlantCode=b.PlantCode And a.ItemCode=b.ItemCode
Inner Join PlantItemVAssignDetail c On c.AssignCode=b.AssignCode And c.VendorCode=a.VendorCode
Where b.InvalidDate>=to_number(to_char(Sysdate,'YYYYMMDD'))
) View3 On MRDemand.CompanyCode=View3.CompanyCode And MRDemand.PlantCode=View3.PlantCode
And MRDemand.ItemCode=View3.ItemCode
Left Join (
Select ASN.COMPANYCODE,ASN.PLANTCODE,ASNDetail.ITEMCODE,20070321 PLANDATE,
Sum(ASNDetail.SHIPQTY) SHIPQTY
From ASN,ASNDetail
Where ASN.STNo=ASNDetail.STNo And ASN.STTYPE='JIT'
And ASN.STStatus In ('Release','WaitCheck')
And ASNDetail.STDSTATUS In ('Release','WaitCheck')
And ASNDetail.CHECKSTATUS Not In ('Qualified','UnQualified')
And ASNDetail.SSDate>19010101 And ASNDetail.SSDate<20070321
Group By ASN.COMPANYCODE,ASN.PLANTCODE,ASNDetail.ITEMCODE
) ViewTow
On MRDemand.CompanyCode=ViewTow.CompanyCode And MRDemand.PlantCode=ViewTow.PlantCode
And MRDemand.ItemCode=ViewTow.ItemCode And MRDemand.DemandDate=ViewTow.PLANDATE
Where MRDemand.AdjustQty>0 And MRDemand.VendorCode='*'
And MRDemand.DemandDate Between :V00001 And :V00002
and MRDemand.PlantCode in ('','2000')
Union
Select MRDemand.CompanyCode,MRDemand.PlantCode,MRDemand.ItemCode,MRDemand.DemandDate,
MRDemand.VendorCode,MRDemand.TranCode,
MRDemand.DemandQty,MRDemand.AdjustQty,MRDemand.PreDemandQty,MRDemand.PreAdjustQty,
MRDemand.MRType,MRDemand.PlanUGCode,
MRDemand.PurchUGCode,MRDemand.OverDueQty,
MRDemand.CreateUser,MRDemand.CreateDate,MRDemand.CreateTime,
MRDemand.LogUser, MRDemand.LogDate,MRDemand.LogTime,MRDemand.Addition1,MRDemand.Addition2
From MRDemand
Inner Join (
Select PI.CompanyCode,PI.PlantCode,PI.ItemCode
From Plant2Item PI,ChianTypeDef CTD
Where PI.CompanyCode=CTD.CompanyCode And PI.PlantCode=CTD.PlantCode
And PI.ChianSubType=CTD.ChianSubType And CTD.CHIANTYPE<>'Indirect'
) ViewOne
On MRDemand.CompanyCode=ViewOne.CompanyCode And MRDemand.PlantCode=ViewOne.PlantCode
And MRDemand.ItemCode=ViewOne.ItemCode
Inner Join PlantItem2Vendor On MRDemand.CompanyCode=PlantItem2Vendor.CompanyCode
And MRDemand.PlantCode=PlantItem2Vendor.PlantCode And MRDemand.ItemCode=PlantItem2Vendor.ItemCode
And MRDemand.VendorCode=PlantItem2Vendor.VendorCode
Where MRDemand.AdjustQty>0 And MRDemand.VendorCode<>'*' And MRDemand.DemandDate Between :V00003
And :V00004 and MRDemand.PlantCode in ('','2000')
優化後的測試SQL:
Select /*+ ordered use_hash(t3 d) use_hash(t3 t2) */
d.CompanyCode,d.PlantCode,d.ItemCode,d.DemandDate,d.VendorCode,d.TranCode,d.DemandQty,
Case When (d.AdjustQty-NVL(t2.ShipQty,0))<0 Then 0 Else (d.AdjustQty-NVL(t2.ShipQty,0)) End AdjustQty,
d.PreDemandQty,d.PreAdjustQty,d.MRType,d.PlanUGCode,d.PurchUGCode,d.OverDueQty,
d.CreateUser,d.CreateDate,d.CreateTime,d.LogUser,d.LogDate,d.LogTime,
d.Addition1,d.Addition2
From (
Select Distinct t1.*
From (Select Distinct CompanyCode, PlantCode, ItemCode
From MRDemand
Where DemandDate>=20070322 and PlantCode in ('2000') And VendorCode='*' And AdjustQty>0) t1
Inner Join Plant2Item pi On pi.CompanyCode=t1.CompanyCode And pi.PlantCode=t1.PlantCode And pi.ItemCode=t1.ItemCode
Inner Join ChianTypeDef ct On ct.CompanyCode=pi.CompanyCode
And ct.PlantCode=pi.PlantCode And ct.ChianSubType=pi.ChianSubType
And ct.ChianType In ('Self','Direct') And ct.PlantCode In ('2000')
Inner Join PlantItem2Vendor vi On vi.CompanyCode=t1.CompanyCode And vi.PlantCode=t1.PlantCode
And vi.ItemCode=t1.ItemCode
Inner Join PlantItemVAssign via On via.CompanyCode=t1.CompanyCode And via.PlantCode=t1.PlantCode
And via.ItemCode=t1.ItemCode And via.InvalidDate>=20070322
Inner Join PlantItemVAssignDetail viad On viad.AssignCode=via.AssignCode And viad.VendorCode=vi.VendorCode
) t3
Inner Join MRDemand d On d.CompanyCode=t3.CompanyCode And d.PlantCode=t3.PlantCode And d.ItemCode=t3.ItemCode
Left Join(Select /*+ ordered index(snd PK_ASNDETAIL) */
sn.CompanyCode,sn.PlantCode,snd.ItemCode,20070322 As PlanDate,Sum(snd.ShipQty) As ShipQty
From ASN sn
Inner Join ASNDetail snd On sn.STNO=snd.STNO
Where sn.PlantCode In ('2000') And sn.STStatus In ('Release','WaitCheck') And sn.STType='JIT'
And snd.STDStatus In ('Release','WaitCheck') And snd.CheckStatus Not In ('Qualified','UnQualified')
And snd.SSDate>20070222 And snd.SSDate<20070322
Group By sn.CompanyCode,sn.PlantCode,snd.ItemCode
) t2 On t2.CompanyCode=d.CompanyCode And t2.PlantCode=d.PlantCode
And t2.ItemCode=d.ItemCode And t2.PlanDate=d.DemandDate
Where d.DemandDate>=20070322 and d.PlantCode in ('2000') And d.VendorCode='*' And d.AdjustQty>0
Union All
Select /*+ ordered use_hash(t1 d) */
d.CompanyCode,d.PlantCode,d.ItemCode,d.DemandDate,d.VendorCode,d.TranCode,
d.DemandQty,d.AdjustQty,d.PreDemandQty,d.PreAdjustQty,d.MRType,d.PlanUGCode,
d.PurchUGCode,d.OverDueQty,d.CreateUser,d.CreateDate,d.CreateTime,d.LogUser,
d.LogDate,d.LogTime,d.Addition1,d.Addition2
From
(
Select Distinct PlantCode, CompanyCode, ItemCode, VendorCode
From MRDemand
Where DemandDate>=20070322 And PlantCode in ('2000') And VendorCode<>'*' And AdjustQty>0
) t1
Inner Join PlantItem2Vendor vi On vi.PlantCode=t1.PlantCode And vi.CompanyCode=t1.CompanyCode
And vi.ItemCode=t1.ItemCode And vi.VendorCode=t1.VendorCode
Inner Join Plant2Item pi
On pi.CompanyCode=t1.CompanyCode And pi.PlantCode=t1.PlantCode And pi.ItemCode=t1.ItemCode
Inner Join (
Select Distinct CompanyCode, PlantCode, ChianSubType
From ChianTypeDef
Where ChianType In ('Self','Direct') And PlantCode In ('2000')
) ct On ct.CompanyCode=pi.CompanyCode And ct.PlantCode=pi.PlantCode And ct.ChianSubType=pi.ChianSubType
Inner Join MRDemand d On d.CompanyCode=t1.CompanyCode And d.PlantCode=t1.PlantCode
And d.ItemCode=t1.ItemCode And d.VendorCode=t1.VendorCode
Where d.DemandDate>=20070322 and d.PlantCode in ('2000') And d.VendorCode<>'*' And d.AdjustQty>0
優化後的執行計劃:d.CompanyCode,d.PlantCode,d.ItemCode,d.DemandDate,d.VendorCode,d.TranCode,d.DemandQty,
Case When (d.AdjustQty-NVL(t2.ShipQty,0))<0 Then 0 Else (d.AdjustQty-NVL(t2.ShipQty,0)) End AdjustQty,
d.PreDemandQty,d.PreAdjustQty,d.MRType,d.PlanUGCode,d.PurchUGCode,d.OverDueQty,
d.CreateUser,d.CreateDate,d.CreateTime,d.LogUser,d.LogDate,d.LogTime,
d.Addition1,d.Addition2
From (
Select Distinct t1.*
From (Select Distinct CompanyCode, PlantCode, ItemCode
From MRDemand
Where DemandDate>=20070322 and PlantCode in ('2000') And VendorCode='*' And AdjustQty>0) t1
Inner Join Plant2Item pi On pi.CompanyCode=t1.CompanyCode And pi.PlantCode=t1.PlantCode And pi.ItemCode=t1.ItemCode
Inner Join ChianTypeDef ct On ct.CompanyCode=pi.CompanyCode
And ct.PlantCode=pi.PlantCode And ct.ChianSubType=pi.ChianSubType
And ct.ChianType In ('Self','Direct') And ct.PlantCode In ('2000')
Inner Join PlantItem2Vendor vi On vi.CompanyCode=t1.CompanyCode And vi.PlantCode=t1.PlantCode
And vi.ItemCode=t1.ItemCode
Inner Join PlantItemVAssign via On via.CompanyCode=t1.CompanyCode And via.PlantCode=t1.PlantCode
And via.ItemCode=t1.ItemCode And via.InvalidDate>=20070322
Inner Join PlantItemVAssignDetail viad On viad.AssignCode=via.AssignCode And viad.VendorCode=vi.VendorCode
) t3
Inner Join MRDemand d On d.CompanyCode=t3.CompanyCode And d.PlantCode=t3.PlantCode And d.ItemCode=t3.ItemCode
Left Join(Select /*+ ordered index(snd PK_ASNDETAIL) */
sn.CompanyCode,sn.PlantCode,snd.ItemCode,20070322 As PlanDate,Sum(snd.ShipQty) As ShipQty
From ASN sn
Inner Join ASNDetail snd On sn.STNO=snd.STNO
Where sn.PlantCode In ('2000') And sn.STStatus In ('Release','WaitCheck') And sn.STType='JIT'
And snd.STDStatus In ('Release','WaitCheck') And snd.CheckStatus Not In ('Qualified','UnQualified')
And snd.SSDate>20070222 And snd.SSDate<20070322
Group By sn.CompanyCode,sn.PlantCode,snd.ItemCode
) t2 On t2.CompanyCode=d.CompanyCode And t2.PlantCode=d.PlantCode
And t2.ItemCode=d.ItemCode And t2.PlanDate=d.DemandDate
Where d.DemandDate>=20070322 and d.PlantCode in ('2000') And d.VendorCode='*' And d.AdjustQty>0
Union All
Select /*+ ordered use_hash(t1 d) */
d.CompanyCode,d.PlantCode,d.ItemCode,d.DemandDate,d.VendorCode,d.TranCode,
d.DemandQty,d.AdjustQty,d.PreDemandQty,d.PreAdjustQty,d.MRType,d.PlanUGCode,
d.PurchUGCode,d.OverDueQty,d.CreateUser,d.CreateDate,d.CreateTime,d.LogUser,
d.LogDate,d.LogTime,d.Addition1,d.Addition2
From
(
Select Distinct PlantCode, CompanyCode, ItemCode, VendorCode
From MRDemand
Where DemandDate>=20070322 And PlantCode in ('2000') And VendorCode<>'*' And AdjustQty>0
) t1
Inner Join PlantItem2Vendor vi On vi.PlantCode=t1.PlantCode And vi.CompanyCode=t1.CompanyCode
And vi.ItemCode=t1.ItemCode And vi.VendorCode=t1.VendorCode
Inner Join Plant2Item pi
On pi.CompanyCode=t1.CompanyCode And pi.PlantCode=t1.PlantCode And pi.ItemCode=t1.ItemCode
Inner Join (
Select Distinct CompanyCode, PlantCode, ChianSubType
From ChianTypeDef
Where ChianType In ('Self','Direct') And PlantCode In ('2000')
) ct On ct.CompanyCode=pi.CompanyCode And ct.PlantCode=pi.PlantCode And ct.ChianSubType=pi.ChianSubType
Inner Join MRDemand d On d.CompanyCode=t1.CompanyCode And d.PlantCode=t1.PlantCode
And d.ItemCode=t1.ItemCode And d.VendorCode=t1.VendorCode
Where d.DemandDate>=20070322 and d.PlantCode in ('2000') And d.VendorCode<>'*' And d.AdjustQty>0
執行計劃中收縮起來的部分，基本都是INDEX UNIQUE SCAN，其它部分INDEX RANGE SCAN的，可以確定掃描的範圍很小，保證INDEX的使用效率很高。