WITH AS and materialize hints

加--+materialize提示，Oracle會先將查詢結果寫入一張臨時表，然再查詢臨時表。

WITH AS： 就是將一個子查詢部分獨立出來，有時候是為了提高SQL語句的可讀性，有時候是為了提高SQL語句效能。  

          如果一個SQL語句中，某個表會被訪問多次，而且每次訪問的限制條件一樣的話，就可以使用with as來提高效能。  

          注意：如果 with as 短語沒有被呼叫2次以上，CBO就不會將 with as短語獲取的資料放入temp表，如果想要將資料放入temp表需要使用materialize hint  

                如果 with as 短語被呼叫了2次以上，CBO會自動將 with as 短語的資料放入一個臨時表，這個時候不用寫materialize hint  

  

舉個例子(本例基於Scott使用者)  

SQL> explain plan for with a as (select /*+ materialize */ ename,job,deptno from emp where sal>(select avg(sal) from emp)) select * from a ;  

Explained.  

  

SQL> select * from table(dbms_xplan.display);  

  

  

去掉 /*+ materialize */ ，由於只訪問了一次a,所以CBO不會將a的查詢結果生成一個臨時表  

  

SQL> explain plan for with a as (select ename,job,deptno from emp where sal>(select avg(sal) from emp))  select * from a ;   

  

Explained.  

  

SQL> select * from table(dbms_xplan.display);  

  

  

如上：WITH AS 語句呼叫一次 使用多次需要寫hints。

  

 

繼續測試:  

SQL> explain plan for with a as (select ename,job,deptno from emp where sal>(select avg(sal) from emp)) select * from a union all select * from a;   

  

Explained.  

  

SQL> select * from table(dbms_xplan.display);  

  

充分證明 :  

1.當with as 語句沒有被呼叫2次以上時,如果需要訪問多次,那麼需要加hints  /*+ materialize */   

  

2.如果with as 語句被呼叫2次以上時,自動會將 with as 短語的資料放入一個臨時表，這個時候不用寫materialize hint.

轉自： WITH AS and materialize hints_ITPUB部落格