在與朋友談論with as語句的時候，網上說with as會生成臨時表這種說法是不負責的。
下面我們做個小例子

點選(此處)摺疊或開啟

with a as (select * from dept) select * +MATERIALIZE * b.empno ,a.dname from emp b,a where a.deptno=b.deptno and a.deptno=20;

這是他的執行計劃，如果說cbo給你做臨時表了，那麼可能是由於oracle的一個bug給群眾誤導了... ...你想要知道到底給沒給你做臨時表看10053哦。

10053 中把這個sql做成了
SELECT "B"."EMPNO" "EMPNO","DEPT"."DNAME" "DNAME"
FROM "SCOTT"."EMP" "B","SCOTT"."DEPT" "DEPT"
WHERE "B"."DEPTNO"=20 AND "DEPT"."DEPTNO"=20

下面做了個hint走物化檢視，查詢這個的時候3min都沒出來，我還以為sql上引起的問題... ...

id=6處是系統生成的物化檢視

看系統磁碟滿了，暈，當時做實驗會話sid是1

materialize這個hint是oracle沒有公開的提示，它指示ORACLE CBO在with子句內物化臨時表。這在oracle10g之後是沒有必要的，但是實際使用with時，如果發現沒有被物化，可以用此hint來強制oracle作此處理，使此子查詢（臨時表）只被處理一次。

結論：
在一定的情況下，我們可以用with與hint materialize結合的方式取代全域性臨時表和物化檢視來提高查詢效率，使用情況如下。

1.    當次查詢的結果記錄數不是太多的情況下，如果結果記錄集比較龐大，還是推薦使用GTT，因為那裡面可以指定索引等。
2.    查詢值和查詢過濾條件中如果含有重複執行的表示式，可以用此方式減少一次計算。
3.    相同子查詢被多次使用。

with後的子查詢如果多次被呼叫，並且oracle沒有作必要的transform那麼，我們可以用hint讓其物化住，如果一次呼叫，那麼沒必要做hint哦。

with as探討時小插曲

相關文章