Master Oracle Hierarchical Queries: From Basics to Advanced Techniques

1. Basic Concepts

Hierarchical queries retrieve data that has an implicit tree structure, such as organizational charts, family trees, or product assemblies. The key idea is to start from a root node and traverse parent‑child relationships using START WITH and CONNECT BY clauses.

Understand the basic concept of hierarchical queries.

Build and format a tree report.

Prune nodes and branches.

Master keywords and new 10g features.

Keywords: tree, root, node, leaf, branch

2. Hierarchical Query Syntax

The query consists of two main components: START WITH – defines the root (or roots) of the hierarchy. CONNECT BY PRIOR – defines the parent‑child relationship and traversal direction.

Example using the s_emp table:

SELECT LEVEL, id, manager_id, last_name, title
FROM s_emp
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

Key points: LEVEL is optional and indicates the depth of each row. WHERE filters rows after the hierarchy is built; it does not affect the tree structure.

Without START WITH, every row is treated as a root.

3. Common Clauses and Operators

Clauses: START WITH, CONNECT BY, ORDER SIBLINGS BY (10g).

Operators: PRIOR, CONNECT_BY_ROOT (10g).

Pseudo‑columns: LEVEL, CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF.

Parameters/Keywords: NOCYCLE (10g).

Functions: SYS_CONNECT_BY_PATH.

4. Traversal Direction and Root Selection

The direction of traversal is controlled by the placement of PRIOR:

Top‑down: CONNECT BY PRIOR parent_id = child_id.

Bottom‑up: CONNECT BY child_id = PRIOR parent_id. START WITH determines the root node; if omitted, each row becomes a root.

5. Formatting Reports with LEVEL and LPAD

Use LEVEL together with LPAD to indent rows and create a readable tree view:

SELECT LEVEL, id, manager_id,
       LPAD(last_name, LENGTH(last_name) + (LEVEL*2) - 2, '_') AS indented_name,
       title,
       PRIOR last_name AS parent_name
FROM s_emp
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

Variations using different prefixes (e.g., '|--', '|('||LEVEL||')--') produce alternative visual styles.

6. Pruning Branches

Adding conditions to CONNECT BY can prune entire sub‑trees efficiently, avoiding the need to filter after the hierarchy is built. For example, adding AND id != 2 removes the branch rooted at id = 2 and all its descendants.

7. Practical Applications

Count nodes per level:

SELECT COUNT(DISTINCT LEVEL) FROM s_emp
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

Format hierarchical reports: Use LPAD with LEVEL as shown above.

Find if a specific employee is managed by another:

SELECT LEVEL, a.* FROM s_emp a
WHERE first_name = 'Elena'
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

Delete a sub‑tree (e.g., layoff a department):

DELETE FROM s_emp WHERE id IN (
  SELECT id FROM s_emp
  START WITH id = 2
  CONNECT BY PRIOR id = manager_id
);

Find each department’s manager:

SELECT LEVEL, a.* FROM s_emp a
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id AND dept_id != PRIOR dept_id;

Aggregate salaries in a sub‑tree:

SELECT SUM(salary) FROM s_emp
START WITH id = 2
CONNECT BY PRIOR id = manager_id;

8. Advanced Features (10g and later)

SYS_CONNECT_BY_PATH: Returns the full path from the root to the current node.

SELECT last_name,
       LEVEL,
       LPAD(' ', LEVEL*2-1) || SYS_CONNECT_BY_PATH(last_name, '=>')
FROM s_emp
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

CONNECT_BY_ISLEAF: Pseudo‑column that identifies leaf nodes (1 = leaf, 0 = non‑leaf).

CONNECT_BY_ISCYCLE and NOCYCLE: Detect and prevent cycles in the hierarchy.

SELECT * FROM t
START WITH a = 1
CONNECT BY NOCYCLE PRIOR b = a;

CONNECT_BY_ROOT: Returns the root column value for each row.

SELECT CONNECT_BY_ROOT last_name AS root_name,
       CONNECT_BY_ROOT id AS root_id,
       id, last_name, manager_id
FROM s_emp
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

ORDER SIBLINGS BY: Preserves hierarchy while sorting siblings within each level.

SELECT LEVEL, id, last_name, manager_id
FROM s_emp
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id
ORDER SIBLINGS BY last_name;

9. Example Images

10. Summary

Oracle hierarchical queries enable powerful tree‑structured data retrieval using START WITH and CONNECT BY. Understanding the role of LEVEL, pseudo‑columns, and 10g enhancements such as SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF, NOCYCLE, and ORDER SIBLINGS BY allows developers to build flexible reports, perform aggregate calculations, prune unwanted branches, and safely navigate complex relationships.