ITPub博客

WITH AS and materialize hints

Oracle 作者:不一样的天空w 时间:2018-11-09 12:19:14 0 删除 编辑

加--+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.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2219367/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-09-22

  • 博文量
    732
  • 访问量
    340471