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.