WITH AS and materialize hints

WITH AS： 就是將一個子查詢部分獨立出來，有時候是為了提高SQL語句的可讀性，有時候是為了提高SQL語句效能。          如果一個SQL語句中，某個表會被訪問多次，而且每次訪問的限制條件一樣的話，就可以使用with as來提高效能。          注意：如果 with as 短語沒有被調用2次以上，CBO就不會講這個短語擷取的資料放入temp表，如果想要講資料放入temp表需要使用materialize hint                如果 with as 短語被調用了2次以上，CBO會自動將 with as 短語的資料放入一個暫存資料表，這個時候不用寫materialize hint舉個例子(本例基於Scott使用者)SQL> explain plan forwith a as (select /*+ materialize */ ename,job,deptno from emp where sal>(select avg(sal) from emp))select * from a ;  2    3Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------Plan hash value: 2006423466-------------------------------------------------------------------------------------------------------| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |                          |     1 |    26 |     8   (0)| 00:00:01 ||   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |            |          ||   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6605_E16CE |       |       |            |          ||*  3 |    TABLE ACCESS FULL       | EMP                      |     1 |    21 |     3   (0)| 00:00:01 ||   4 |     SORT AGGREGATE         |                          |     1 |     4 |            |          ||   5 |      TABLE ACCESS FULL     | EMP                      |    14 |    56 |     3   (0)| 00:00:01 ||   6 |   VIEW                     |                          |     1 |    26 |     2   (0)| 00:00:01 ||   7 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6605_E16CE |     1 |    17 |     2   (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP"))19 rows selected.去掉 /*+ materialize */ ，由於只訪問了一次a,所以CBO不會將a的查詢結果產生一個暫存資料表SQL> explain plan forwith a as (select ename,job,deptno from emp where sal>(select avg(sal) from emp))select * from a ;  2    3Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------Plan hash value: 1876299339----------------------------------------------------------------------------| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT    |      |     1 |    21 |     6   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL  | EMP  |     1 |    21 |     3   (0)| 00:00:01 ||   2 |   SORT AGGREGATE    |      |     1 |     4 |            |          ||   3 |    TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP"))15 rows selected.WITH AS 語句調用一次 使用多次 需要寫hints如果  表 只 掃描 1次,你些materialize hints 結果讀了一次 還寫入temp, 再從temp讀出來暫存資料表寫入是1次,但是讀要多次。繼續測試:SQL> explain plan forwith a as (select ename,job,deptno from emp where sal>(select avg(sal) from emp))select * from a union all select * from a;  2    3  Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2575088720--------------------------------------------------------------------------------------------------------| Id  | Operation   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |       |     2 |    52 |     4(50)| 00:00:01 ||   1 |  TEMP TABLE TRANSFORMATION |       |       |       |    |       ||   2 |   LOAD AS SELECT   | SYS_TEMP_0FD9D6601_4DC46A |       |       |    |       ||*  3 |    TABLE ACCESS FULL   | EMP       |     1 |    39 |     3 (0)| 00:00:01 ||   4 |     SORT AGGREGATE   |       |     1 |    13 |    |       ||   5 |      TABLE ACCESS FULL   | EMP       |    14 |   182 |     3 (0)| 00:00:01 ||   6 |   UNION-ALL   |       |       |       |    |       ||   7 |    VIEW    |       |     1 |    26 |     2 (0)| 00:00:01 ||   8 |     TABLE ACCESS FULL   | SYS_TEMP_0FD9D6601_4DC46A |     1 |    26 |     2 (0)| 00:00:01 ||   9 |    VIEW    |       |     1 |    26 |     2 (0)| 00:00:01 ||  10 |     TABLE ACCESS FULL   | SYS_TEMP_0FD9D6601_4DC46A |     1 |    26 |     2 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP"))Note-----   - dynamic sampling used for this statement (level=2)26 rows selected.充分證明 :1.當with as 語句沒有被調用2次以上時,如果表需要訪問多次,那麼需要加hints  /*+ materialize */ 2.如果with as 語句被調用2次以上時,自動會將 with as 短語的資料放入一個暫存資料表，這個時候不用寫materialize hint