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?
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