ORACLE WITH與性能優化

with as能夠做到更好性能的原理是把一段查詢sql先執行，把擷取的資料放入temp表。

with as性能優化主要有兩方面：

一方面，當一個with as定義的表名被調用兩次以上時，oracle優化器會自動建立一個temp表，該with as語句隻需要執行一遍，即可引用多次。如下兩段代碼，功能是一樣的，但使用with as後tmp和tmp1語句在第一段皆隻需執行一次，在第二段裡tmp語句執行了兩次而tmp1語句執行一次。

WITH TMP AS 
(SELECT * FROM TABLE1),
TMP1 AS 
(SELECT * FROM TABLE2 T2 JOIN TMP ON T2.ID=TMP.ID)
SELECT * 
  FROM TABLE3 T3 
  JOIN TMP 
    ON T3.ID=TMP.ID 
  LEFT JOIN TMP1 T1 
    ON T3.ID=T1.ID；

SELECT * 
  FROM TABLE3 T3 
  JOIN (SELECT * FROM TABLE1) AS TMP
    ON T3.ID=TMP.ID 
  LEFT JOIN (SELECT * FROM TABLE2 T2 
                      JOIN (SELECT * FROM TABLE1) AS TMP 
                        ON T2.ID=TMP.ID
            )T1 
    ON T3.ID=T1.ID；
           

另一方面，當一個with as定義的表名隻被調用一次時，oracle優化器不會自動建立一個temp表，但因優化器原因導緻性能變慢時，需要使用hint關鍵字強制轉換成temp表進行優化。如下代碼，當join tmp2時性能慢時可把TMP2強制轉換成temp表（有時把join改left join也有同樣效果）

WITH TMP1 AS
(SELECT * FROM TABLE1),
TMP2 AS
(SELECT /*+ MATERIALIZE*/ * FROM TABLE2 T2 JOIN TMP1 ON T2.ID=TMP1.ID)
SELECT * FROM TABLE3 T3 JOIN TMP2 ON T3.ID=TMP2.ID
           

/+ materialize/ 優化

hint 關鍵字	描述
	會強制性要求 with as 中的結果轉換為 臨時表
	與上相反，不轉換