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?

Tom Kyte overview linked. Hierarchical queries are best example. Employees and their managers. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421 — Brian, Mar 20 '21 at 11:40
Row `(a, b)` doesn't have any parent/child relationship to row `(b, c)`. It will be easier to demonstrate working with hierarchies with hierarchical data, rather than just using `connect by` (not necessarily `connect by level` - personally I prefer to `connect by rownum`) as a row generation hack. — William Robertson, Mar 20 '21 at 11:41
@WilliamRobertson what would be the difference between connecting by level vs by rownum? — Javi Torre, Mar 20 '21 at 15:15
It's not exactly *'connecting by level'*. The syntax is `connect by` followed by an expression which is evaluated for each iteration. (The documentation suggests the `prior` keyword is required, although this is not enforced, which is why this is sometimes regarded as a hack.) `rownum <= 3` is true for the first three rows so you'll get 3 rows back. `level <= 3` is true for the first three *levels*, which depends on the set you are starting with. Since you start with more than one row and you are asking it to generate all hierarchies for every row, you get an exponential number of rows back. — William Robertson, Mar 20 '21 at 15:37
@WilliamRobertson if I replace in the query of my question the word 'level' by the word'rownum' it returns 4 rows. Why is that? — Javi Torre, Mar 20 '21 at 17:56
I'm not sure, to be honest. It generates the 3 rows for col1 = `a`, plus one more for `b`. Normally when we use `connect by` without a `prior` or `start with` condition, we are using it to generate multiple rows from the 1 row in dual, and it doesn't make any sense to base it on a multi-row set as you get this meaningless exponential result. Really, `start with` and `prior` are the whole point of `connect by`, and using it without them as a row generator is a hack, albeit a convenient one. — William Robertson, Mar 21 '21 at 09:58

MT0 · Accepted Answer · 2021-03-20T12:12:15.007

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

Oracle SQL start with/prior

1 Answers1