Re: 9i "SQL WITH" clause ?

Interesting! Thanks Mark for the details.

Student-

"Mark Townsend" <markbtownsend_at_home.com> wrote in message news:B7594596.A1C%markbtownsend_at_home.com...
> 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:06 CDT