努力提高此程式碼的效率

查詢視圖內的列（需要更快地工作）

(
SELECT SUM(COALESCE(ft8.dPercent, 1.00000000000000000000) 
 * COALESCE(ft7.dPercent, 1.00000000000000000000) 
 * COALESCE(ft6.dPercent, 1.00000000000000000000) 
 * COALESCE(ft5.dPercent, 1.00000000000000000000) 
 * COALESCE(ft4.dPercent, 1.00000000000000000000) 
 * COALESCE(ft3.dPercent, 1.00000000000000000000) 
 * COALESCE(ft2.dPercent, 1.00000000000000000000) 
 *ft1.dPercent 
 * 100.00000000)
FROM ODS.YARDI_Property px
 INNER JOIN ODS.YARDI_Fund_Tran ft1 ON px.hMy = ft1.hInvestor
   AND ft1.iCategory = 0
   AND ft1.dPercent <> 0
   AND SYSDATE BETWEEN ft1.dtStart AND nvl(ft1.dtEnd, SYSDATE+1)
 LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft2 ON ft1.hInvestment <> P.hMy
   AND ft1.hInvestment = ft2.hInvestor
   AND ft2.iCategory = 0
   AND ft2.dPercent <> 0
   AND SYSDATE BETWEEN ft2.dtStart AND COALESCE(ft2.dtEnd, SYSDATE)
 LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft3 ON ft2.hInvestment <> P.hMy
   AND ft2.hInvestment = ft3.hInvestor
   AND ft3.iCategory = 0
   AND ft3.dPercent <> 0
   AND SYSDATE BETWEEN ft3.dtStart AND COALESCE(ft3.dtEnd, SYSDATE)
 LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft4 ON ft3.hInvestment <> P.hMy
   AND ft3.hInvestment = ft4.hInvestor
   AND ft4.iCategory = 0
   AND ft4.dPercent <> 0
   AND SYSDATE BETWEEN ft4.dtStart AND COALESCE(ft4.dtEnd, SYSDATE)
 LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft5 ON ft4.hInvestment <> P.hMy
   AND ft4.hInvestment = ft5.hInvestor
   AND ft5.iCategory = 0
   AND ft5.dPercent <> 0
   AND SYSDATE BETWEEN ft5.dtStart AND COALESCE(ft5.dtEnd, SYSDATE)
 LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft6 ON ft5.hInvestment <> P.hMy
   AND ft5.hInvestment = ft6.hInvestor
   AND ft6.iCategory = 0
   AND ft6.dPercent <> 0
   AND SYSDATE BETWEEN ft6.dtStart AND COALESCE(ft6.dtEnd, SYSDATE)
 LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft7 ON ft6.hInvestment <> P.hMy
   AND ft6.hInvestment = ft7.hInvestor
   AND ft7.iCategory = 0
   AND ft7.dPercent <> 0
   AND SYSDATE BETWEEN ft7.dtStart AND COALESCE(ft7.dtEnd, SYSDATE)
 LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft8 ON ft7.hInvestment <> P.hMy
   AND ft7.hInvestment = ft8.hInvestor
   AND ft8.iCategory = 0
   AND ft8.dPercent <> 0
   AND SYSDATE BETWEEN ft8.dtStart AND COALESCE(ft8.dtEnd, SYSDATE)
WHERE --P.HMY>4643
 trim(TRAILING ' ' FROM lower(px.sCode)) = 'rdrtinc'
 AND p.hMy IN (
   SELECT FT8.hInvestment
     FROM dual
   UNION ALL SELECT FT7.hInvestment
     FROM dual
   UNION ALL SELECT FT6.hInvestment
     FROM dual
   UNION ALL SELECT FT5.hInvestment
     FROM dual
   UNION ALL SELECT FT4.hInvestment
     FROM dual
   UNION ALL SELECT FT3.hInvestment
     FROM dual
   UNION ALL SELECT FT2.hInvestment
     FROM dual
   UNION ALL SELECT FT1.hInvestment
     FROM dual
 )
) AS dOwnershipPercent

步驟 0：刪除標量子查詢。

為結果集中的每一行執行標量子查詢。

將其移至 CTE ( WITH) 並與查詢的其餘部分連接

你可以做幾件事。

1 - 您將函式和不等於放入您的聯接中。這效率不高，因為它無法利用索引。您應該將它們移至where 子句。

2 - 最好用兩個子句重寫不等於零 (<>0)，一個大於零 (>0) 和一個小於零 (<0)。這是降低查詢成本的邊際改進。

3 - 盡量避免使用函式。在您的程式碼中，可以將between 函式重寫為大於和小於，從而進一步降低查詢成本。

4 - 確保您有正確的索引：

ODS.YARDI_Fund_Tran >> Compound Index on **hInvestor** and **iCategory**
ODS.YARDI_Fund_Tran >> Compound Index on **dPercent**, **dtStart**, **dtEnd**
ODS.YARDI_Fund_Tran >> Index on **hInvestment**
ODS.YARDI_Property >> Index on **hMy**

不確定查詢優化器是否會全部使用它們。測試看看哪些給你最好的影響。

您的程式碼摘錄：

LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft2 ON ft1.hInvestment <> P.hMy
   AND ft1.hInvestment = ft2.hInvestor
   AND ft2.iCategory = 0
   AND ft2.dPercent <> 0
   AND SYSDATE BETWEEN ft2.dtStart AND COALESCE(ft2.dtEnd, SYSDATE)

我會將其重寫為：

LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft2 ON 
   ft1.hInvestment = ft2.hInvestor
   AND ft2.iCategory = 0

WHERE ft1.hInvestment <> P.hMy 
   AND ft2.dPercent > 0
   AND ft2.dPercent < 0
   AND ft2.dtStart >= SYSDATE
   AND COALESCE(ft2.dtEnd, SYSDATE) <= SYSDATE 

將此應用於您的每個條款並註意差異。

引用自：https://dba.stackexchange.com/questions/311686