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关键字/*+ materialize*/强制转换成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 关键字	描述
/*+ materialize*/	会强制性要求 with as 中的结果转换为 临时表
/*+ inline*/	与上相反，不转换