在与朋友谈论with as语句的时候，网上说with as会生成临时表这种说法是不负责的。
下面我们做个小例子

点击(此处)折叠或打开

with a as (select * from dept) select * +MATERIALIZE * b.empno ,a.dname from emp b,a where a.deptno=b.deptno and a.deptno=20;

这是他的执行计划，如果说cbo给你做临时表了，那么可能是由于oracle的一个bug给群众误导了... ...你想要知道到底给没给你做临时表看10053哦。

10053 中把这个sql做成了
SELECT "B"."EMPNO" "EMPNO","DEPT"."DNAME" "DNAME"
FROM "SCOTT"."EMP" "B","SCOTT"."DEPT" "DEPT"
WHERE "B"."DEPTNO"=20 AND "DEPT"."DEPTNO"=20

下面做了个hint走物化视图，查询这个的时候3min都没出来，我还以为sql上引起的问题... ...

id=6处是系统生成的物化视图

看系统磁盘满了，晕，当时做实验会话sid是1

materialize这个hint是oracle没有公开的提示，它指示ORACLE CBO在with子句内物化临时表。这在oracle10g之后是没有必要的，但是实际使用with时，如果发现没有被物化，可以用此hint来强制oracle作此处理，使此子查询（临时表）只被处理一次。

结论：
在一定的情况下，我们可以用with与hint materialize结合的方式取代全局临时表和物化视图来提高查询效率，使用情况如下。

1.    当次查询的结果记录数不是太多的情况下，如果结果记录集比较庞大，还是推荐使用GTT，因为那里面可以指定索引等。
2.    查询值和查询过滤条件中如果含有重复执行的表达式，可以用此方式减少一次计算。
3.    相同子查询被多次使用。

with后的子查询如果多次被调用，并且oracle没有作必要的transform那么，我们可以用hint让其物化住，如果一次调用，那么没必要做hint哦。

ITPub博客

with as探讨时小插曲

最新文章