oracle跑with语句很慢,oracle 11g新特性---WITH语句增强

--with语句增强，11gR2增强

SQL> select * from test;

ID NAME                 ADDR

---------- -------------------- --------------------

1 wzk                  chengdu

2 cherry               chengdu

3 wzk                  heilongjiang

4 cherry               beijing

--with语句增加了别名，但是个数和顺序与as后面的子查询一一对应。

SQL> with c(id,name) as

2  (select id,name,addr from test where id=1 or id=2)

3  select * from c;

(select id,name,addr from test where id=1 or id=2)

*

ERROR at line 2:

ORA-32038: number of WITH clause column names does not match number of elements

in select list

--也可以看出select查询之后，返回的列名就是我们在with子句中定义的别名。

SQL> with c(no,name,address) as

2  (select id,name,addr from test where id=1 or id=2)

3  select * from c;

NO NAME