CONNECT BY, Recursive WITH, Hierarchical Data, Cycle Handling, and Tree Traversal

Learning outcomes

ServiceHub stores components in an adjacency list: each component optionally points to a parent component. A malformed import creates a cycle, while another report sorts all rows with a normal ORDER BY and destroys the tree’s sibling structure. Oracle offers two strong ways to traverse this data: its long-standing START WITH ... CONNECT BY syntax and recursive subquery factoring with SEARCH/CYCLE.

1. Model the hierarchy explicitly

The simplest relational tree stores a row identifier plus a nullable parent identifier. The root has no parent; every non-root row points to its parent. A self-referencing foreign key can enforce that the referenced parent exists, but it does not automatically prove the graph is acyclic. Cycles are a data-quality rule that traversal logic must detect or prevent.

CREATE TABLE servicehub_sql_component (    component_id        NUMBER PRIMARY KEY,    parent_component_id NUMBER,    component_name      VARCHAR2(80) NOT NULL,    CONSTRAINT sh05_component_parent_fk      FOREIGN KEY (parent_component_id)      REFERENCES servicehub_sql_component(component_id));

2. CONNECT BY: root, parent-child rule, depth, and path

START WITH selects root rows. CONNECT BY defines how a parent row relates to its child. The unary PRIOR operator identifies which expression comes from the parent row. LEVEL is a pseudocolumn reporting depth, beginning at 1 for each root.

SELECT    component_id,    parent_component_id,    component_name,    LEVEL AS depth,    SYS_CONNECT_BY_PATH(component_name, '/') AS pathFROM servicehub_sql_componentSTART WITH parent_component_id IS NULLCONNECT BY PRIOR component_id = parent_component_idORDER SIBLINGS BY component_name;

SYS_CONNECT_BY_PATH makes the ancestor chain visible. ORDER SIBLINGS BY sorts children under each parent while preserving hierarchical structure. A normal top-level ORDER BY component_name can flatten the display order and obscure the traversal.

3. Deliberately wrong: introduce a cycle and observe ORA-01436

A cycle exists when following parent/child relationships eventually returns to an ancestor. In the disposable lab, make the root a child of one of its descendants. The ordinary CONNECT BY query now detects a loop.

UPDATE servicehub_sql_componentSET parent_component_id = 3WHERE component_id = 1;COMMIT;SELECT component_id, component_name, LEVELFROM servicehub_sql_componentSTART WITH component_id = 1CONNECT BY PRIOR component_id = parent_component_id;-- ORA-01436: CONNECT BY loop in user data

This is not a performance problem. It is a graph-integrity problem exposed by traversal.

SELECT    component_id,    component_name,    LEVEL AS depth,    CONNECT_BY_ISCYCLE AS is_cycle,    SYS_CONNECT_BY_PATH(component_name, '/') AS pathFROM servicehub_sql_componentSTART WITH component_id = 1CONNECT BY NOCYCLE PRIOR component_id = parent_component_id;

NOCYCLE allows rows to be returned despite the loop, while CONNECT_BY_ISCYCLE marks a row whose child is also its ancestor. Use this to investigate; do not normalize cyclic data as a valid tree merely because NOCYCLE can render it.

UPDATE servicehub_sql_componentSET parent_component_id = NULLWHERE component_id = 1;COMMIT;

4. Recursive subquery factoring: anchor + recursive member

Current Oracle SQL also supports recursive subquery factoring. The factored query’s column alias list is required for recursion. One branch is the anchor member that selects roots; the recursive branch references the query name and joins children to rows already produced.

WITH component_tree    (component_id, parent_component_id, component_name, depth, path) AS (    SELECT        component_id,        parent_component_id,        component_name,        1,        '/' || component_name    FROM servicehub_sql_component    WHERE parent_component_id IS NULL    UNION ALL    SELECT        c.component_id,        c.parent_component_id,        c.component_name,        t.depth + 1,        t.path || '/' || c.component_name    FROM servicehub_sql_component c    JOIN component_tree t      ON c.parent_component_id = t.component_id)SEARCH DEPTH FIRST BY component_name SET traversal_orderCYCLE component_id SET cycle_mark TO 'Y' DEFAULT 'N'SELECT    component_id,    parent_component_id,    component_name,    depth,    path,    cycle_markFROM component_treeORDER BY traversal_order;

The SEARCH clause generates an ordering column for depth-first or breadth-first traversal. The CYCLE clause defines which columns identify a cycle and adds a cycle marker. If you omit CYCLE and Oracle discovers a recursive cycle, the statement errors rather than recursing forever.

5. CONNECT BY versus recursive WITH

Choose based on readability, existing code, required features, and team familiarity. Do not rewrite a correct CONNECT BY query merely because recursive WITH looks newer.

6. Performance and correctness boundaries

Index the parent reference when the workload repeatedly finds children by parent_component_id; this is a normal access-path consideration, not a universal tuning mandate. Constrain roots and traversal depth when the business question permits it. For large graphs, measure actual row counts and path fan-out because a valid tree can still expand dramatically.

Do not confuse a hierarchy with a general graph problem. A component can have one parent in this adjacency-list model. If the domain needs arbitrary many-to-many edges, graph modeling or an explicit edge table may be more accurate than forcing the data into a tree.

7. Hands-on lab: build, break, diagnose, repair, and traverse

DROP TABLE servicehub_sql_component IF EXISTS PURGE;CREATE TABLE servicehub_sql_component (    component_id        NUMBER PRIMARY KEY,    parent_component_id NUMBER,    component_name      VARCHAR2(80) NOT NULL,    CONSTRAINT sh05_component_parent_fk      FOREIGN KEY (parent_component_id)      REFERENCES servicehub_sql_component(component_id));CREATE INDEX sh05_component_parent_ixON servicehub_sql_component(parent_component_id);INSERT INTO servicehub_sql_component VALUES (1,NULL,'Cooling System');INSERT INTO servicehub_sql_component VALUES (2,1,'Pump Group');INSERT INTO servicehub_sql_component VALUES (3,2,'Pump A');INSERT INTO servicehub_sql_component VALUES (4,2,'Pump B');INSERT INTO servicehub_sql_component VALUES (5,1,'Valve Group');INSERT INTO servicehub_sql_component VALUES (6,5,'Valve A');COMMIT;

SELECT    LPAD(' ', 2 * (LEVEL - 1)) || component_name AS tree_line,    LEVEL AS depth,    SYS_CONNECT_BY_PATH(component_name, '/') AS pathFROM servicehub_sql_componentSTART WITH parent_component_id IS NULLCONNECT BY PRIOR component_id = parent_component_idORDER SIBLINGS BY component_name;

DROP TABLE servicehub_sql_component PURGE;

Verification checklist:

• The initial root is Cooling System at LEVEL=1.
• Pump A and Pump B are siblings below Pump Group.
• The deliberate cycle produces ORA-01436 without NOCYCLE.
• CONNECT_BY_ISCYCLE or recursive CYCLE can make cycle evidence visible.
• The repaired tree is acyclic and the cleanup touches only the probe table.

8. Production judgment and chapter bridge

Production hierarchy code needs a documented root rule, parent-key rule, cycle policy, traversal order, and expected fan-out. A foreign key proves parent existence but not acyclicity. Decide whether cycles should be blocked at write time, detected in validation, or tolerated only in non-tree graph data. Monitor pathological depth/fan-out rather than prescribing a universal maximum.

No paid option, management pack, restart, or COMPATIBLE change is required for these SQL hierarchy examples on the declared 26ai baseline. This closes the fundamentals chapter: you now have Oracle-specific SELECT, join, subquery, set, and hierarchy semantics. Chapter 06 builds on that foundation with analytic functions, advanced aggregation, MATCH_RECOGNIZE, MODEL, and SQL macros.

Authoritative references

• SELECT — recursive subquery factoring — recursive WITH, SEARCH, and CYCLE syntax
• SQL Language Reference — current 26ai hierarchical and recursive query grammar
• Hierarchical Query Pseudocolumns — LEVEL, CONNECT_BY_ISCYCLE, and related pseudocolumns
• Joins — join/access-path context for recursive members