Search notes:

Oracle SQL: Hierarchical queries / START WITH … CONNECT BY

select … START WITH initial-condition CONNECT BY [ NOCYCLE ] recurse-condition

select … CONNECT BY [ NOCYCLE ] recurse-condition
Oracle's SQL extension start with and connect by can be used to create a query on data that has a hierarchical (parent-child such as superior-employee) relationship among records (that is hierarchical queries).

PRIOR clause

The relationship between the parent record and its child records is expressed by the prior keyword: 
PRIOR child_id = parent_id
MOS Note 1617780.1 says that using connect by without prior clause is not allowed and may lead to the error message. ORA-30009: Not enough memory for CONNECT BY operation (but see also the error message ORA-30008: CONNECT BY clause must reference a PRIOR)

Interpreting start with … connnect by prior statements

The following sort-of-pseudo code demonstrates how a start with … connect by statement might be interpreted. 
declare

  procedure RECURSE (rec in MATCHES_SELECT_STMT, parent_id IN field_type) is
  begin

       APPEND_RESULT_LIST(rec);     

       for rec_recurse in (select * from some_table) loop
           if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.id, parent_id) then
              RECURSE(rec_recurse,rec_recurse.id);
           end if;
       end loop;

  end procedure RECURSE;

begin

  for rec in (select * from some_table) loop
      if FULLFILLS_START_WITH_CONDITION(rec) then
         RECURSE(rec, rec.child);
      end if;
  end loop;

end;
Thanks to Frank Trenkamp who spotted an error in the logic in the above pseudo code and corrected it.
Thanks also to Abhishek Ghose who made me think about a better way to describe the logic.

Examples

recursive additions
pruning branches
Check if two records are in a generaral ancestor descendant relationship, not just (immediate) parent child.

TODO

With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child
connect_by_root, connect_by_isleaf, connect_by_iscycle

Thanks

Thanks to Peter Bruhn, Jonathan Schmalze, Jeff Jones, Keith Britch and Fabian Iturralde who each pointed out an error or typo on this page.

See also

Using Common Table Expressions (CTEs) to formulate hierarchical queries.
Hierarchical queries are also used to explain execution plans.
The where clause in hierarchical queries is evaluated after selecting records with start with … connect by.
The error mesasages
Event 10131
Using connect by and/or start with in a view prevents the view from being updatable.
Generate an arbitrary number of records with the connect by clause.

Links

On storing hierarchical data
On summing up values of nodes of a hierarchical query

Index