Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
I expect I'm just missing something, but if I run this query without the "connect by", I get 2 rows. When I add "connect by level <= 4", I would expect to get each of those 2 rows 4 times. The actual result is different.
Can anyone help me understand what's happening here? I'm not looking for a solution that only repeats each row 4 times - I've already got that. I'm just looking to understand what's happening and why.
with alpha as (
select 1 as id
from dual
),
beta as (
select 1 as alpha_id,
1 as beta_no
from dual
union all
select 1 as alpha_id,
2 as beta_no
from dual
)
select a.id,
b.beta_no,
level as the_level
from alpha a
inner join beta b
on b.alpha_id = a.id
connect by level <= 4
order by a.id,
b.beta_no,
level
;
ID BETA_NO THE_LEVEL
1 1 1 1 1 2 1 1 2 1 1 3 1 1 3 1 1 3 1 1 3 1 1 4 1 1 4 1 1 4 1 1 4 1 1 4 1 1 4 1 1 4 1 1 4 1 2 1 1 2 2 1 2 2 1 2 3 1 2 3 1 2 3 1 2 3 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 2 430 rows selected
Many thanks to mathguy. The second link he provided in the answer below had exactly what I was looking for. Specifically:
1 with t as (select 1 as id from dual union all
2 select 2 from dual)
3 --
4 select id, level
5 ,prior id
6 ,sys_connect_by_path(id,'=>') as cpath
7 from t
8* connect by level <= 3
SQL> /
ID LEVEL PRIORID CPATH
---------- ---------- ---------- --------------------------------------------------
1 1 =>1
1 2 1 =>1=>1
1 3 1 =>1=>1=>1
2 3 1 =>1=>1=>2
2 2 1 =>1=>2
1 3 2 =>1=>2=>1
2 3 2 =>1=>2=>2
2 1 =>2
1 2 2 =>2=>1
1 3 1 =>2=>1=>1
2 3 1 =>2=>1=>2
2 2 2 =>2=>2
1 3 2 =>2=>2=>1
2 3 2 =>2=>2=>2
14 rows selected.
It's clear to me from that example, but I'd be hard-pressed to succinctly put it into words.
With no condition other than "level <= 4", every row from the original table, view etc. (from the join, in this case) will produce two rows at level 2, then four more rows at level 3, and 8 more at level 4. "Connect by" is essentially a succession of joins, and you are doing cross joins if you have no condition with the PRIOR operator.
You probably want to add "and prior a.id = a.id". This will lead to Oracle complaining about cycles (because Oracle decides a cycle is reached when it sees the same values in the columns subject to PRIOR). That, in turn, is solved by adding a third condition, usually "and prior sys_guid() is not null".
(Edited; the original answer made reference to NOCYCLE, which is not needed when using the "prior sys_guid() is not null" approach.)
This has been discussed recently on OTN: https://community.oracle.com/thread/3999985
Same question discussed here: https://community.oracle.com/thread/2526535