Re: 9i "SQL WITH" clause ?

in article 9h0op2$a0k$1_at_slb2.atl.mindspring.net, Student at kennylim_at_techie.com wrote on 6/22/01 5:50 PM:

>
> Hi All,
>
> Does anyone know the purpose and simple illustration
> of "SQL WITH clause" in Oracle 9i. So far I have no luck
> slogging through the oracle 9i online docs and MetaLinks.
>
> Thanks in advance.
>
> Student-
>
>
>

Sure - from the SQl Reference manual

> subquery_factoring_clause
>
> The subquery_factoring_clause (WITH query_name) lets you assign names to
> subquery blocks. You can then reference the subquery block multiple places in
> the query by specifying the query name. Oracle optimizes the query by treating
> the query name as either an inline view or as a temporary table.
>
> You can specify this clause in any top-level SELECT statement and in most
> types of subqueries. The query name is visible to all subsequent subqueries
> (except the subquery that defines the query name itself) and to the main
> query.
>
> Restrictions:
>
> * You cannot nest this clause. That is, you cannot specify the
> subquery_factoring_clause as a subquery within another
> subquery_factoring_clause.
>
> * In a query with set operators, the set operator subquery cannot contain the
> subquery_factoring_clause, but the FROM subquery can contain the
> subquery_factoring_clause.

As an example, from the Application Developer Guide

> In complex queries that process the same subquery multiple times, you might be
> tempted to store the subquery results in a temporary table and perform
> additional queries against the temporary table. The WITH clause lets you
> factor out the subquery, give it a name, then reference that name multiple
> times within the original complex query.
>
> This technique lets the optimizer choose how to deal with the subquery results
> -- whether to create a temporary table or inline it as a view.
>
> For example, the following query joins two tables and computes the aggregate
> SUM(SAL) more than once. The bold text represents the parts of the query that
> are repeated.
>
>
> SELECT dname, SUM(sal) AS dept_total
> FROM emp, dept
> WHERE emp.deptno = dept.deptno
> GROUP BY dname HAVING
> SUM(sal) >
> (
> SELECT SUM(sal) * 1/3
> FROM emp, dept
> WHERE emp.deptno = dept.deptno
> )
> ORDER BY SUM(sal) DESC;
>
>
>
> You can improve the query by doing the subquery once, and referencing it at
> the appropriate points in the main query. The bold text represents the common
> parts of the subquery, and the places where the subquery is referenced.
>
>
> WITH
> summary AS
> (
> SELECT dname, SUM(sal) AS dept_total
> FROM emp, dept
> WHERE emp.deptno = dept.deptno
> GROUP BY dname
> )
> SELECT dname, dept_total
> FROM summary
> WHERE dept_total >
> (
> SELECT SUM(dept_total) * 1/3
> FROM summary
> )
> ORDER BY dept_total DESC;
  Received on Sat Jul 21 2001 - 16:58:02 CDT