Oracle Connect By seems to produce too many rows

Question

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          4

30 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.

score 2 · Accepted Answer · 2017-01-11T13:46:17.790

2

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

edited Jan 11 '17 at 13:46

answered Jan 11 '17 at 01:01

Thanks mathguy and Boneist. As I said, I'm not looking for a fix, I'm just trying to understand what causes it. I think I'll play around with the connect by path and see if I can figure it out... – Matt Knowles Jan 12 '17 at 01:56
@MattKnowles - I didn't offer that as a fix to your query - I offered that as (part of) the explanation of what you saw. If you will follow the links, and further links from there, you will see the discussion is pretty much about "how this works", not "how it's used to solve a specific problem." Playing with it yourself, as you are planning to do, will also help a lot for sure. Good luck! – Jan 12 '17 at 02:18
Thanks again for your time, mathguy. – Matt Knowles Jan 13 '17 at 04:15

Boneist · Answer 2 · 2017-01-11T11:46:46.727

To illustrate Mathguy's answer, you are missing some predicates out of your CONNECT BY clause:

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
           AND PRIOR a.id = a.id
           AND PRIOR b.beta_no = b.beta_no
           AND PRIOR sys_guid() IS NOT NULL
order by a.id,
         b.beta_no,
         LEVEL;

        ID    BETA_NO  THE_LEVEL
---------- ---------- ----------
         1          1          1
         1          1          2
         1          1          3
         1          1          4
         1          2          1
         1          2          2
         1          2          3
         1          2          4

An alternative would be to use the recursive with clause:

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
    ),
    multiply (id, beta_no, rn) AS (SELECT a.id,
                                          b.beta_no,
                                          1 rn
                                   FROM   alpha a
                                          INNER JOIN beta b
                                            ON a.id = b.alpha_id
                                   UNION ALL
                                   SELECT ID,
                                          beta_no,
                                          rn + 1
                                   FROM   multiply
                                   WHERE  rn + 1 <= 4)
SELECT ID,
       beta_no,
       rn AS the_level
FROM   multiply
order by id,
         beta_no,
         rn;

        ID    BETA_NO  THE_LEVEL
---------- ---------- ----------
         1          1          1
         1          1          2
         1          1          3
         1          1          4
         1          2          1
         1          2          2
         1          2          3
         1          2          4

Oracle Connect By seems to produce too many rows

2 Answers2