Oracle WITH AS 用法

1.子查询因子从句

WITH AS从句为子查询块分配一个查询名字（query_name）。通过指定查询名称可以在查询中多次使用该子查询块。Oracle数据库把查询名字当作是一个内嵌视图或临时表来优化查询。

可以在任何查询语句的顶层和多数子查询类型中指定子查询因子从句。查询名称对主查询和除了定义子查询名字外的所有后续子查询是可见。

2.子查询因子限制

• 在单个SQL语句中指定定义一个子查询，不能在查询名字中定义自身的子查询。然而，在子查询因子中定义的查询名称可以用在后续命名查询块的子查询因子中。
• 在一个复合查询集合运算中，不能使用查询名字作为任何查询的组件，但可以用查询名称作为FORM从句的任何查询组件。

 

3.语法

WITH query_name AS (subquery)
     [, query_name AS (subquery) ]...

 

4.EXAMPLE

使用scott用户登录，按照要求查询结果。

4.1.不使用子查询因子从句，查询总工资小于平均工资的部门

select d.dname, sum(e.sal) dept_total
  from emp e, dept d
 where e.deptno = d.deptno having
 sum(e.sal) < (select sum(dept_total) / count(1)
                       from (select sum(e.sal) dept_total
                               from emp e, dept d
                              where e.deptno = d.deptno
                              group by d.dname))
 group by d.dname

 

查询结果，需要两次扫描emp和deprt.

 

4.2使用WITH AS子查询因子从句，查询总工资小于平均工资的部门

with dept_costs as
 (select d.dname, sum(e.sal) dept_total
    from emp e, dept d
   where e.deptno = d.deptno
   group by d.dname),
avg_cost as
 (select sum(dept_total) / count(1) avg from dept_costs)
select *
  from dept_costs
 where dept_total < (select avg from avg_cost)

查询结构，只需要一次扫描emp和deprt,将子查询的结果存储到dept_costs和avg_cost临时表中。

 

结论：

1.如果查询结果在后续的查询语句中多次被使用，子查询因子从句会大大提升效率。子查询因子从句查询的结果会被保存到临时表中，该临时表会随着查询的结束而消失。如果子查询因子从句查询的结果集非常大，会占用很大的内存。因此当子查询结果集很大，不建议使用子查询因子从句。