1

Topic: Question on connect by

Children all greetings!
Can explain algorithm of operation of hierarchical requests? In particular the following example:

With Test (Name) as
(select ' Tom ' from dual)
select Name, level from Test
connect by level <=2

Returns that I and expected: one record is deduced two times on hierarchy (1*2) = 2
Name, Level
Tom, 1
Tom, 2
Further I add one more record (tuple) in the table and I fulfill the same request.
On my waitings: sampling of the table is first of all fulfilled, further to it the condition in Connect by is applied. And, as it is not specified root parent each record is considered. I.e. for each record it will be returned two records according to condition Connect by. . I expect that returns: 1*2 + 1*2 = 4 records. But returned:

With Test (Name) as
(select ' Tom ' from dual
union
select ' Jerry ' from dual)
select Name, level from Test
connect by level <=2

Name, Level
Jerry, 1
Jerry, 2
Tom, 2
Tom, 1
Jerry, 2
Tom, 2
6 records returned! Can explain why ? And how there was an algorithm?

2

Re: Question on connect by

SQL_boy wrote:

  6 records! Can explain why ? And how there was an algorithm?

All are children of all.

3

Re: Question on connect by

SQL_boy wrote:

6 records Returned! Can explain why ? And how there was an algorithm?

To begin with it is necessary to realize that you did not specify a connection condition.
By analogy to the Cartesian product, connect by in these conditions tries to connect all to all.
Total: 2 records level 1 and on two  level 2 at everyone. 2 * (1+2 =6

4

Re: Question on connect by

andrey_anonymous wrote:

it is passed...
To begin with it is necessary to realize that you did not specify a connection condition.
By analogy to the Cartesian product, connect by in these conditions tries to connect all to all.
Total: 2 records level 1 and on two  level 2 at everyone. 2 * (1+2 =6

Children of thanks for answers! Like at first considered that understood. And started to check and understood that did not understand smile
Now I will sound the course of thought and it is parallel the questions which have arisen in process.
There is a table (relation), containing two record (tuple). We apply to it sampling with connect by:

With Test (Name) as
(select ' Tom ' from dual
union
select ' Jerry ' from dual)
select Name, level from Test
connect by level <=2

So, according to  :

Oracle wrote:

1) Oracle selects the root row (s) of the hierarchy - those rows that satisfy the START WITH condition.
2) Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

As in request explicitly it is not specified, what record is parent (root) it is considered all records root.

' Tom ' - - Root
' Jerry ' - - Root

We take the first record - ' Tom ', it at us root and as I understand conditions of connection CONNECT BY to it is not applied (correct if not so), and this condition to all subsequent records is applied. In our case we take the following record Jerry and already to it we apply a condition connect by level <=2. Here I thought that relational operation - product (Cartesian) is fulfilled: Jerry * 2 (..connect by level <=2). As a result we receive the relation from two tuples. . at us one record root - Tom (level=1), plus two its descendants (two records Jerry level 2), Total three records turns out:
Name, Level
' Tom ', 1
' Jerry ', 2
' Jerry ', 2
Further passes to the second root records - Jerry and the algorithm repeats. We receive also three records:
Name, Level
' Jerry ', 1
' Tom ', 2
' Tom ', 2
On an output we receive result which I resulted in the first post of this subject:
Name, Level
Jerry, 1
Jerry, 2
Tom, 2
Tom, 1
Jerry, 2
Tom, 2
Then I decided to rewrite a condition connect by, instead of 2 delivered 3:

With Test (Name) as
(select ' Tom ' from dual
union
select ' Jerry ' from dual)
select Name, level from Test
connect by level <=3

I expected that 8 records returns: (Tom (root) + 3 descendants (Jerry)) + (Jerry (root) + 3 descendants Tom). But the result was such:
Name, Level
Jerry, 1
Jerry, 2
Jerry, 3
Tom, 3
Tom, 2
Jerry, 3
Tom, 3
Tom, 1
Jerry, 2
Jerry, 3
Tom, 3
Tom, 2
Jerry, 3
Tom, 3
14 records! Not so I understood Something, my algorithm was erratic! what not so?

5

Re: Question on connect by

SQL_boy;
Once again, you do not have relation the parent - the descendant in a condition connections (operator PRIOR);
when passage to new level all records in the table is fulfilled correspond to a connection condition.
Passage to new level is fulfilled will not be constructed yet two levels.
At passage to new level all records of the table are children of all nodes of level.
When it you realize - you can pass to the request analysis.

With Test (Name) as
(select ' Tom ' from dual
union
select ' Jerry ' from dual)
select Name, level from Test
connect by>>> rownum <<<<=3