Difference caused by placement of PRIOR statement in an Oracle hierarchical query

Question

Seems you can do both the following in Oracle when working with Connect By.

 CONNECT BY NOCYCLE 
    parent_id = PRIOR child_r_object_id

 CONNECT BY NOCYCLE PRIOR 
    parent_id = child_r_object_id

What is the difference, most examples on line tend to use the second syntax, but both execute.

Answer 1

Basically you define to build the hierarchy from top-to-down or from down-to-top.

Have a look at this examples to see the difference:

WITH t(person, parent_id, ID) AS (
    SELECT 'Grandma', NULL, 1 FROM dual
    UNION ALL SELECT 'Mother', 1, 10 FROM dual
    UNION ALL SELECT 'Daughter', 10, 100 FROM dual
    UNION ALL SELECT 'Son', 10, 101 FROM dual)
SELECT person AS leaf_person, CONNECT_BY_ROOT(person) AS top_person, 
       SYS_CONNECT_BY_PATH(person, '->'), 'down' AS direction
FROM t
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR ID
UNION ALL
SELECT person as leaf_person, CONNECT_BY_ROOT(person) as top_person, 
       SYS_CONNECT_BY_PATH(person, '->'), 'up' AS direction
FROM t
WHERE CONNECT_BY_ISLEAF = 1
START WITH ID IN (100,101)
CONNECT BY PRIOR parent_id = ID;


+-----------------------------------------------------------------+
|LEAF_PERSON|TOP_PERSON|SYS_CONNECT_BY_PATH(PERSON,'->')|DIRECTION|
+-----------------------------------------------------------------+
|Daughter   |Grandma   |->Grandma->Mother->Daughter     |down     |
|Son        |Grandma   |->Grandma->Mother->Son          |down     |
|Grandma    |Daughter  |->Daughter->Mother->Grandma     |up       |
|Grandma    |Son       |->Son->Mother->Grandma          |up       |
+-----------------------------------------------------------------+

Typically you have just one root (i.e. parent_id IS NULL) or at least determined root elements, thus most examples use the "top-to-down" direction.