Re: feature & performance comparison

From: Mark Townsend <markbtownsend_at_home.com>
Date: Mon, 11 Jun 2001 01:52:47 GMT
Message-ID: <B749727F.1536B%markbtownsend@home.com>

in article 3B241AD0.A760206E_at_ca.ibm.com, Blair Kenneth Adamache at adamache_at_ca.ibm.com wrote on 6/10/01 6:11 PM:

> It starts with a Common Table Expression (the WITH):
>
> WITH RPL (PART, SUBPART, QUANTITY) AS
> ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
> FROM PARTLIST ROOT
> WHERE ROOT.PART = '01'
> UNION ALL
> SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
> FROM RPL PARENT, PARTLIST CHILD
> WHERE PARENT.SUBPART = CHILD.PART
> )
> SELECT DISTINCT PART, SUBPART, QUANTITY
> FROM RPL
> ORDER BY PART, SUBPART, QUANTITY
>
> Mark Townsend wrote:
>

>> in article 3B2266AA.13D8BF0E_at_ca.ibm.com, Blair Kenneth Adamache at
>> adamache_at_ca.ibm.com wrote on 6/9/01 11:10 AM:
>> 
>>> Does Oracle have recursive SQL?
>> 
>> I'm not exactly sure what you mean by recursive SQL - do you have an example
>> ? Oracle does have some extensions for tree traversal - CONNECT BY - but I'm
>> not sure this is what you mean.

>

Ah - named subquery blocks - yes, new in Oracle9i. As a syntax example see below. I'm away from an instance to show an explain plan at this time but basically the named subquery block is transformed into an inline view or a temporary table (depending on cost advantage of substantiating the query vs the advantage of predicate push down into the view etc ) and re-used.

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 Sun Jun 10 2001 - 20:52:47 CDT