Oracle SQL start with/prior

Question

I've successfully mananaged to understand how the connect by level works with the below example:

SELECT
    level,
    t.*
FROM
    (
        SELECT
            'a' AS col1,
            'b' AS col2
        FROM
            dual
        UNION ALL
        SELECT
            'c',
            'd'
        FROM
            dual
    ) t
CONNECT BY
    level <= 3

However, I'm struggling to understand the 'start with' and 'prior' concepts and what use cases do they have in real life. Could someone please walk me through using the provided example?

Answer 1

If you have a parent/child relationship:

CREATE TABLE t ( parent, child ) AS
  SELECT 'a', 'b' FROM dual UNION ALL
  SELECT 'b', 'c' FROM dual UNION ALL
  SELECT 'c', 'd' FROM dual UNION ALL
  SELECT 'd', 'e' FROM dual;

And you want to get the family tree starting from b and get all of the descendants then you can:

SELECT level,
       t.*
FROM   t
START WITH parent = 'b'
CONNECT BY PRIOR child = parent

Which outputs:

LEVEL | PARENT | CHILD
----: | :----- | :----
    1 | b      | c    
    2 | c      | d    
    3 | d      | e    

Level 1 starts with b then level 2 has b's child c then level 3 has b's child's child (grandchild) d and they are all connected by the relationship that the PRIOR child is the (current) parent.

More examples of how to get different relationships can be found in this answer.

---

As an aside, your example in the question is a little confusing as it is finding all paths to a depth of 3 recursions. If you show the paths it has taken through the data using SYS_CONNECT_BY_PATH then you get a better idea:

SELECT level,
       t.*,
       SYS_CONNECT_BY_PATH( '('||col1||','||col2||')', '->' ) AS path
FROM (
  SELECT 'a' AS col1, 'b' AS col2 FROM dual UNION ALL
  SELECT 'c', 'd' FROM dual
) t
CONNECT BY level <= 3

Which outputs:

LEVEL | COL1 | COL2 | PATH                 
----: | :--- | :--- | :--------------------
    1 | a    | b    | ->(a,b)              
    2 | a    | b    | ->(a,b)->(a,b)       
    3 | a    | b    | ->(a,b)->(a,b)->(a,b)
    3 | c    | d    | ->(a,b)->(a,b)->(c,d)
    2 | c    | d    | ->(a,b)->(c,d)       
    3 | a    | b    | ->(a,b)->(c,d)->(a,b)
    3 | c    | d    | ->(a,b)->(c,d)->(c,d)
    1 | c    | d    | ->(c,d)              
    2 | a    | b    | ->(c,d)->(a,b)       
    3 | a    | b    | ->(c,d)->(a,b)->(a,b)
    3 | c    | d    | ->(c,d)->(a,b)->(c,d)
    2 | c    | d    | ->(c,d)->(c,d)       
    3 | a    | b    | ->(c,d)->(c,d)->(a,b)
    3 | c    | d    | ->(c,d)->(c,d)->(c,d)

You get 14 rows because you get 2 rows at level 1 (one for each combination of input row) and then 4 rows at level 2 (one for each input row for each level 1 row) and then 8 rows at level 2 (one for each input row for each level 2 row) and your output is growing exponentially.

db<>fiddle here