Oracle with as语句的DDL尴尬

文章由LinuxBoy分享于2019-03-30 05:03:35热评（454）

Oracle with as语句的DDL尴尬

Oracle with as 的用法不赘述，我理解有两方面的好处

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;

建个表，插条数

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

推荐文章：

Oracle with as语句的DDL尴尬