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 for  
with a as (select /*+ materialize */ ename,job,deptno from emp where sal>(select avg(sal) from emp))  
select * from a ;  
  2    3  
Explained.  
  
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 for  
with a as (select ename,job,deptno from emp where sal>(select avg(sal) from emp))  
select * from a ;  2    3  
  
Explained.  
  
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 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;  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