Oracle Hierarchy Query: Fix Menu Sorting with CONNECT BY

Problem: Why ORDER BY Breaks Menu Trees

When sorting menu data, it's common to write:

ORDER BY MENU_DPTH, DISP_PRIR_SEQ

This looks reasonable—but it breaks tree structures.

Because ORDER BY does not understand relationships between rows.

---

## Environment

- Oracle Database
- Menu table with parent-child relation
- Root nodes defined by `HGRK_MENU_NO = 0`

---

## Root Cause

ORDER BY performs **flat sorting only**.

Example:

10 (parent)  
20 (parent)  
11 (child of 10)  
21 (child of 20)

Result:

10  
20  
11 ❌ misplaced  
21

Children are no longer attached to their parent.

---

## Solution: CONNECT BY

Use Oracle’s hierarchy query:

SELECT  
       M.MENU_NO  
     , M.MENU_NM  
     , M.HGRK_MENU_NO  
     , LEVEL  
FROM   TCOMM_MENU_INF M  
START WITH M.HGRK_MENU_NO = 0  
CONNECT BY PRIOR M.MENU_NO = M.HGRK_MENU_NO  
ORDER SIBLINGS BY M.DISP_PRIR_SEQ;

-- Build hierarchy recursively and sort siblings

---

## Why CONNECT BY Works

CONNECT BY:

- Traverses parent → child relationships
- Builds the tree structure
- Executes recursively

This is not sorting—this is **structure construction**.

---

## What ORDER SIBLINGS BY Does

ORDER SIBLINGS BY DISP_PRIR_SEQ

- Sorts only nodes with the same parent
- Preserves the hierarchy

---

## Result Comparison

### Wrong

10  
20  
11  
21

### Correct

10  
  11  
20  
  21

---

## Key Takeaway

Whenever you handle tree data, ask:

> "Am I sorting data, or building a structure?"

If it's a structure, ORDER BY alone will fail.