Can I use the Connect By operator to generate a set?

user576036

The Connect By operator is a way to handle hierarchical models implemented through tables.

Is it possible to use it generate sets like in CTE (or recursive subquery factoring)?

Frank Kulash

Hi,

user576036 wrote:

Sorry, I should have made myself more clear..

The Connect By is using with hierarchical tables: we take such a table, and find all the managers of a worker or all the sub ordinates of a manager etc.
The CTE has also the ability to take a degenerated select from Dual and create from it a set of all the numbers up to ...
My question is if there is a way to use the Connect By to create a set from Dual, or it is impossible, and it works only with hierarchical tables?

It is obvious that the CTE can do whatever Connect By can,
but is it also true to the other side?

Another question- in which year was released the first Oracle version which supports CTE?

Thank you very much!
 

CONNECT BY was designed for adjacency-model trees, like you described.  In my earlier message, I said that that was one area where CONNECT BY might be better than a recursive WITH clause.  If I gave the impression that CONNECT BY isn't good at anything else, I apologize.  It's very useful for other things, and better than recursive WITH clauses for many of them.

For example, CONNECT BY can be used to generate a "counter" table, for example, to get 10 rows, contain the integers 1 through 10:

SELECT  LEVEL  AS n
FROM    dual
CONNECT BY  LEVEL  <= 10;
 

This is actally a common use of CONNECT BY, and it's very efficient.

I don't think that CONNECT BY can do anything that a recursive WITH clause can do.  Maybe CONNECT BY together with MODEL, or some XML functions, can do anything that a recursive WITH clause can do, but it might be much more difficult and much less efficient.

WITH clauses were new in Oracle 9.1, released in 2001.

Recursive WITH clauses were new in Oracle 11.2, released in 2009.

I find it interesteing that CONNECT BY was built into Oracle 2, released in 1979.

Frank Kulash

Hi,

Sorry, it's unclear what you mean.

Any query (whether it uses CONNECT BY, a recursive WITH clause, both or neither) produces a result set.  In that sense, Yes, CONNECT BY can generate sets.

A recursive WITH clause can do anything that CONNECT BY can do, and more.  For certain problems, especially those involving tree structures where you have parent_id and child_id columns, CONNECT BY is easier to debug and use.

It would help if you could post a specific question.  For example, "Using the scott.emp table, I have this recursive WITH query ...  that does this ...  I'm looking for different ways to get the same reults because ... Would CONNECT BY help?  How can I get the same results using CONNECT BY?"

gaverill

I don't have a good example for you, but you should be able to combine a hierarchical query and a recursive CTE like so to generate a set of, e.g. test data:

with    Generate_Input
as    (
    select    ...
        from    DUAL
        connect by
            level <= :n
        )
,    Recursive_CTE(...)
as    (
    select    ...
        from    Generate_Input i
        union    all
    select    ...
    from    Generate_Input i
    join    Recursive_CTE r on
            ...
    )
select    ...
from    Recursive_CTE
;

FWIW...

Gerard

user576036

Sorry, I should have made myself more clear..

The Connect By is using with hierarchical tables: we take such a table, and find all the managers of a worker or all the sub ordinates of a manager etc.

The CTE has also the ability to take a degenerated select from Dual and create from it a set of all the numbers up to ...

My question is if there is a way to use the Connect By to create a set from Dual, or it is impossible, and it works only with hierarchical tables?

It is obvious that the CTE can do whatever Connect By can,

but is it also true to the other side?

Another question- in which year was released the first Oracle version which supports CTE?

Thank you very much!

Frank Kulash

Hi,

user576036 wrote:

Sorry, I should have made myself more clear..

The Connect By is using with hierarchical tables: we take such a table, and find all the managers of a worker or all the sub ordinates of a manager etc.
The CTE has also the ability to take a degenerated select from Dual and create from it a set of all the numbers up to ...
My question is if there is a way to use the Connect By to create a set from Dual, or it is impossible, and it works only with hierarchical tables?

It is obvious that the CTE can do whatever Connect By can,
but is it also true to the other side?

Another question- in which year was released the first Oracle version which supports CTE?

Thank you very much!
 

CONNECT BY was designed for adjacency-model trees, like you described.  In my earlier message, I said that that was one area where CONNECT BY might be better than a recursive WITH clause.  If I gave the impression that CONNECT BY isn't good at anything else, I apologize.  It's very useful for other things, and better than recursive WITH clauses for many of them.

For example, CONNECT BY can be used to generate a "counter" table, for example, to get 10 rows, contain the integers 1 through 10:

SELECT  LEVEL  AS n
FROM    dual
CONNECT BY  LEVEL  <= 10;
 

This is actally a common use of CONNECT BY, and it's very efficient.

I don't think that CONNECT BY can do anything that a recursive WITH clause can do.  Maybe CONNECT BY together with MODEL, or some XML functions, can do anything that a recursive WITH clause can do, but it might be much more difficult and much less efficient.

WITH clauses were new in Oracle 9.1, released in 2001.

Recursive WITH clauses were new in Oracle 11.2, released in 2009.

I find it interesteing that CONNECT BY was built into Oracle 2, released in 1979.