Re: feature & performance comparison

On Mon, 11 Jun 2001 02:30:02 GMT, Mark Townsend <markbtownsend_at_home.com> wrote:

>
>>> 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
>
>Hmm - actually, there is more to this than I first saw - presumably this
>iterates down through all levels of child ? How is this transformed ? Can
>you push predicates into the traversal ? This may be way cool.
>

It IS kool. Quite a nice syntax too.
I wonder if this has anything to do with Ted Codd's claim many years ago he had found a way of doing the parts explosion with pure SQL, ie, without the CONNECT BY cop-out.

Mind you, I still prefer the CONNECT BY: I don't have to use a DISTINCT to get the parts explosion. DISTINCT is not good for performance in any database. But this is a nice way of doing it. I wonder what happens if there is more than one root? As in multiple parts and their makeup in a single table.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Mon Jun 11 2001 - 04:48:58 CDT