WITH Clause

----------
Forwarded message ----------
From: XIE WEN-MFK346
<wenxie at motorola.com>
Date:
2008/10/28
Subject: WITH子句(WITH
Clause)
To: xiewenxiewen at gmail.com

也叫子查询分解(subquery
factoring)子句，或公用表表达式(common
table expression)

是SQL99的标准, Oracle
9i开始支持

在一个复杂查询中，如果同样的查询块被调用了多次，就可以考虑使用WITH子句

WITH子句定义了子查询的别名，你能够在查询中引用这个别名多次，这样提高了SQL语句的可读性，有时也能提高运行效能

语法是:

WITH

别名1
AS (子查询1)

别名2
AS (子查询2)

...

SELECT ...

优化器对WITH子句有两种处理:

a.WITH子句被当成内嵌视图(inline
view)处理

语句中别名的部分被替换成子查询，SELECT语句被扩展成带有子查询的语句，然后运行

b.或实例化(materialize)，即建立临时表(temporary
table)

一般，如果别名被调用了多次，Oracle会创建全局临时表(global
temporary table)用于保存子查询结果，因为子查询不会被计算多次，也就提高了查询性能

举例

创建测试表

create table t1 as
select * from all_objects;
create table t2 as select * from
dba_objects;
analyze table t1 compute statistics for table for all
indexes for all indexed columns;
analyze table t2 compute
statistics for table for all indexes for all indexed columns;

用传统SQL语句查询

set pages 9999
line 140
set autot on
select count(*)
from t1,
(select distinct owner username from t1) owners
where
t1.owner = owners.username
union all
select count(*)

from t2, (select distinct owner username from t1) owners
where
t2.owner = owners.username
/

T1全表扫描了两次，计算distinct
owner

改写成用WITH子句查询

with
owners
as (select distinct owner username from t1)
select count(*) from
t1, owners where t1.owner = owners.username
union all
select
count(*) from t2, owners where t2.owner = owners.username
/

执行计划里出现TEMP
TABLE TRANSFORMATION表明因为子查询调用了两次，所以系统自动为子查询建立了临时表,表名是SYS_TEMP_....

统计信息里consistent
gets+physical reads比不使用WITH子句少，读的次数减少了，说明子查询只计算了一次，对性能提高是有一定效果的

还产生了744的redo
size，主要是因为建立了临时表

可以用优化器提示(hint)强制使用内嵌视图或临时表

加上inline提示使用内嵌视图

set autot off

explain plan for

with
owners
as (select /*+inline*/ distinct owner username from t1)
select
count(*) from t1, owners where t1.owner = owners.username
union
all
select count(*) from t2, owners where t2.owner =
owners.username
/
select * from table (dbms_xplan.display);

加上materialize提示使用临时表

explain plan
for
with
owners as (select /*+materialize*/ distinct
owner username from t1)
select count(*) from t1, owners where
t1.owner = owners.username
/
select * from table
(dbms_xplan.display);

inline,materialize提示在正式文档中没有讲，有时也不一定管用，所以一般不建议使用

WITH子句使用上有一些限制

1.不允许WITH嵌套使用

with
outer_subquery as (
with nested_subquery as (select sysdate
as date_column from dual))
select date_column from outer_subquery;

WITH子句中的子查询里不能再用WITH

但可以用在后面其它别名中

with

subquery1 as (select sysdate as date_column from dual),

subquery2 as (select date_column from subquery1)
select
date_column from subquery2;

2.如果定义了子查询而没有用到，就会出错

with
unused_subquery
as (select dummy from dual)
select sysdate from dual;

必须用，不用都不行

外部链接:

subquery_factoring_clause

difference
between sql with clause and inline
subquery
factoring in oracle 9i
Subquery
Factoring (2) 或
2

Xie Wen (谢文)

Network &
Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box,
8th floor, Motorola Tower,
No.
1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R.
China
e-mail wenxie at motorola.com