1） 复杂的查询会产生很大的sql，with as语法显示一个个中间结果，显得有条理些，可读性提高
2） 前面的中间结果可以被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时可以提升效率
比如 with a as (select* from dba_objects where 某些查询条件),
b as (select * from a where 某些查询条件)
select * from b , a where 某些查询条件;
可以看到中间表a被两次引用，如果不用with as ，中间查询结果a就要运行2次
我的oracle版本为10.2.0.4.0 ，这次单讲with as中间表多次被引用时触发ddl trigger的尴尬
SQL> create table test_ws (c varchar2(1));
Table created
SQL> insert into test_ws values('1');
1 row inserted
SQL> select * from test_ws;
C
-
1
建个表，插条数
SQL> with a as (select* from test_ws ),
2 b as (select * from a )
3 select * from b , a ;
C C
- -
1 1
两次使用中间表a的with as 语句，正常运行
SQL> create or replace TRIGGER lihao2372_test
2 before ddl on database
3 begin
4 raise_application_error(-20908,'这是ddl');
5 end;
6 /
Trigger created
DDL trigger建起来
SQL> create table ttt (c varchar2(1));
create table ttt (c varchar2(1))
ORA-00604: error occurred at recursive SQL level 1
ORA-20908: 这是ddl
ORA-06512: at line 2
不能建表了，ddL trigger 是ok的
SQL> with a as (select* from test_ws where rownum=1),
2 b as (select * from a )
3 select * from b , a ;
with a as (select* from test_ws where rownum=1),
b as (select * from a )
select * from b , a
ORA-00604: error occurred at recursive SQL level 2
ORA-20908: 这是ddl
ORA-06512: at line 2
咦，with as怎么被当成DDL在处理了？
普通的with as
加入一个where （rownum=1）条件
产生了新的系统临时对象，数据库对象的产生意味着DDL
总结：为保证生产库的稳定运行，往往在上班时间限制DDL。恰恰限制了最有价值的那部分with as的应用。这正是我们无法大力推广with as的尴尬之处。
另外还发现复杂的多层with as容易在10.2.0.3.0版本抛出ORA-21780: Maximum number of object durations exceeded.实际上代码中并未发现无穷递归，将报错时的参数保存下来重新运行不能再现。运行不定长的时间后抛出报错
网上有人说是oracle的bug ，要升级到oracle 10.2.0.4 才能解决，同样代码在10.2.0.4 环境运行无此现象，故从之搜到一份Bugs fixed in the 10.2.0.4 Patch Set
其PL/SQL部分：
5924384 ORA-21780 in PLSQL
6061892Memory Leak on ‘PLS non-lib hp’ by recursive PLSQL calls / ORA-4030 AND ORA-21780