Hiyerarşik veriler: CONNECT BY, özyinelemeli CTE, hierarchyid ve yol yöntemi

Oracle START WITH / CONNECT BY PRIOR, LEVEL, SYS_CONNECT_BY_PATH; diğer SQL türlerinde karşılıkları.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Ağaç yapıları

Organizasyon şeması, kategori ağacı, ürün reçetesi (bill of materials), yorum yanıtları… Hepsi bir satırın başka bir satıra “ebeveyn” olarak bağlandığı yapılardır. En yaygın saklama biçimi komşuluk listesidir: employees.manager_id → employees.emp_id.

Oracle: CONNECT BY

Oracle'ın özyinelemeli CTE'den önce de var olan ve hâlâ çok kullanılan sözdizimi:

SELECT LPAD(' ', 2 * (LEVEL - 1)) || first_name AS agac,
       LEVEL,
       SYS_CONNECT_BY_PATH(first_name, '/') AS yol,
       CONNECT_BY_ISLEAF AS yaprak
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY first_name;

Öğe	Anlamı
`START WITH`	Kök satır(lar)
`CONNECT BY PRIOR emp_id = manager_id`	Ebeveynin emp_id'si çocuğun manager_id'sine eşit (yukarıdan aşağı)
`LEVEL`	Derinlik (kök = 1)
`SYS_CONNECT_BY_PATH(sütun, '/')`	Kökten satıra kadar yol
`CONNECT_BY_ROOT sütun`	Satırın kökündeki değer
`CONNECT_BY_ISLEAF`	Yaprak mı (çocuğu yok)?
`ORDER SIBLINGS BY`	Ağaç düzenini bozmadan kardeşleri sırala
`NOCYCLE`	Döngü varsa durdur

Satır üreteci

SELECT LEVEL AS gun_no,
       DATE '2024-12-01' + LEVEL - 1 AS gun
FROM dual
CONNECT BY LEVEL <= 7;

Standart yol: özyinelemeli CTE

WITH RECURSIVE agac AS (
  SELECT emp_id, first_name, manager_id, 1 AS duzey, '/' || first_name AS yol
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.first_name, e.manager_id, a.duzey + 1, a.yol || '/' || e.first_name
  FROM employees e JOIN agac a ON e.manager_id = a.emp_id
)
SELECT duzey, yol FROM agac ORDER BY yol;

Diğer saklama yöntemleri

Yöntem	Nasıl?	Güçlü yanı
Komşuluk listesi	parent_id sütunu	Basit, güncellemesi kolay; derin sorgular özyineleme ister
Yol (materialized path)	`'/1/3/7/'` metni; `WHERE yol LIKE '/1/3/%'`	Alt ağaç sorgusu hızlı; taşıma pahalı
SQL Server `hierarchyid`	`node.IsDescendantOf(@kok)`, `GetLevel()`	Yerleşik tip ve indeks
PostgreSQL `ltree`	`yol <@ 'genel.satis'`	GiST indeksli yol sorgusu
Kapanış tablosu (closure)	(ata, torun, derinlik) satırları	Her sorgu hızlı; yazma maliyeti yüksek

-- SQL Server hierarchyid
SELECT ad, dugum.ToString() AS yol, dugum.GetLevel() AS duzey
FROM dbo.org
WHERE dugum.IsDescendantOf(hierarchyid::Parse('/1/')) = 1;

-- PostgreSQL ltree
CREATE EXTENSION ltree;
SELECT ad FROM kategori WHERE yol <@ 'elektronik.ses';