SQL'de özyinelemeli Common Table Expression (CTE), kendine referans veren bir sorgu aracılığıyla hiyerarşik verileri set tabanlı bir şekilde dolaşmanızı sağlar. Yapı, bir ana üye (genellikle manager_id IS NULL olan kök düğümler) ve bir özyinelemeli üye (CTE'yi tekrar kendisiyle ebeveyn-çocuk ilişkisi üzerinde birleştiren kısım) içerir. Veritabanı motoru, yeni satır döndürülmediği sürece özyinelemeli üyeyi tekrar tekrar çalıştırarak sonuç kümesini aşamalı olarak oluşturur, açık bir yineleme yapısı olmaksızın.
Bu yaklaşım, SQL'in deklaratif doğasından faydalanarak, optimizer'ın genellikle hash veya merge birleştirme algoritmalarını seçmesini sağlar; bu da CURSOR veya WHILE döngülerinin satır bazında işlenmesine kıyasla daha verimlidir. Söz dizimi PostgreSQL ve MySQL'de WITH RECURSIVE, ya da SQL Server'da (burada özyineleme örtük) WITH olarak kullanılır, ardından CTE adı ve sütun listesi gelir.
12,000 çalışanı olan çok uluslu bir şirket, SOX uyum denetimleri için tam raporlama zincirleri oluşturmak zorundaydı. Mevcut sistem, her çalışanın üzerinden geçerek, yöneticilerini bulmak için bir skalar fonksiyonu özyinelemeli olarak çağıran, her bir çalışan için yineleme yapan bir T-SQL CURSOR kullanıyordu ve hiyerarşiyi parça parça oluşturuyordu. Bu işlem, 47 dakika sürdü, Employees tablosunda kilitler oluşturdu ve iş saatlerinde İK güncellemelerini engelledi ve derin hiyerarşileri (mühendislik departmanındaki matris yapısında yaygın olan) 100 seviyeyi aşan özyinelemeli yığın taşması hatalarıyla sık sık başarısız oldu.
Çözüm A: Geçici tablolarla optimize edilmiş CURSOR. Ekip, önce sonuçları bir geçici tabloya dökmeyi ve ardından sonunda toplu olarak eklemeyi düşünmeye karar verdi. Bu, kilitlenme süresini 47 dakikadan yaklaşık 40 dakikaya düşürecekti. Artıları: Minimum kod değişikliği, eski takım için tanıdık bir desen. Eksileri: Yine satır bazında işlem, yine derin özyinelemeli yığın taşmalarına karşı hassas, sadece performans sorununu hafifletir, çözmez.
Çözüm B: HierarchyID veri tipi. Tabloyu SQL Server'ın yerel HierarchyID türüne geçirmek, ağaç pozisyonlarını dolaşmaya optimize edilmiş kodlanmış yollar olarak saklamaktadır. Artıları: O(1) alt ağaç alma, GetAncestor() ve GetDescendant() gibi yerleşik yöntemler, okuma yoğun yükler için son derece hızlıdır. Eksileri: Muazzam şemaya göç gerektirir (12,000 satır ve geçmiş veriler), yeniden yapılandırmalar sırasında bakımını karmaşıklaştırır (bir yöneticiyi güncellemek, tüm soy bağı yollarını yeniden hesaplamayı gerektirir), şirket PostgreSQL'ye geçmeyi düşünürken SQL Server'a kapatılmış durumdadır.
Çözüm C: Döngü tespiti ile özyinelemeli CTE. Çalışan tablosunu kendi üzerine manager_id ile birleştiren, döngüsel referansların (veri giriş hataları nedeniyle iki kez meydana geldiği) sonsuz döngüleri önlemek için ziyaret edilen düğümleri takip eden bir yol dizisi kullanan özyinelemeli bir CTE uygulamak. Artıları: Saf ANSI SQL standardı (geçiş sırasında PostgreSQL'ye taşınabilir), set tabanlı yürütme süresini 4 dakika 12 saniyeye düşürdü, yürütme sırasında tablo kilitleri tutulmadı (anlık izleme izolasyonu kullanıyor), yığın taşması olmadan keyfi derinlikleri işler, veri kalitesi sorunlarını (döngüleri) otomatik olarak tespit eder.
Ekip Çözüm C'yi seçti. Uygulama, PostgreSQL'in dizi toplama (ya da SQL Server'da dize birleştirme) özelliğini kullanarak çalışan kimliklerini biriken bir path sütunuyla toplaydı, yeni manager_id'nin mevcut yol dizisinde olmadığını kontrol eden bir WHERE koşulu ekledi. Sonuç, %91'lik bir performans artışı, üretim kilitlerinin ortadan kaldırılması ve daha önce uygulama çökmelerine neden olan döngüsel raporlama ilişkilerinin erken tespiti oldu.
Özyinelemeli bir CTE neden sona erer ve veri döngüsel bir referansa sahip olduğunda ne olur?
Adaylar sıklıkla özyinelemeli CTE'lerin yerleşik döngü tespiti olduğunu düşünür, ancak standart SQL özyinelemesi yalnızca özyinelemeli üye sıfır yeni satır döndürdüğünde sona erer. Eğer A Çalışanı B'ye, B C'ye, C A'ya rapor ederse, CTE sonsuz çalışır (veya SQL Server'ın varsayılan 100 özyineleme seviyeleri gibi uygulama sınırlarına ulaşana kadar). Çözüm, ziyaret edilen düğüm kimliklerini bir yol sütununda biriktirerek elle döngü tespiti gerektirir (diziler veya ayrılmış dizeler kullanarak) ve WHERE new_id != ALL(path_array) filtresini uygular. Modern PostgreSQL (14+) ve SQL Server (2022+) standart SQL:1999 CYCLE bölümünü destekler: WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path, bu durumu otomatik olarak yönetir.
Özyinelemeli bir CTE ile bir cursor tabanlı yaklaşım arasındaki yürütme planı nasıl farklıdır ve bu, eşzamanlılık açısından neden önemlidir?
Junior adaylar sıklıkla CTE'lerin "daha hızlı" olduğunu söylerken yürütme modelini anlamazlar. CURSOR'lar SQL Server veya PostgreSQL'de motoru bir sonuç kümesini malzeme haline getirmeye ve satır satır yinelemeye zorlar, genellikle kilitler veya geçici veritabanı kaynaklarını yineleme süresince gerektiren bir Keyset veya Static cursor türü kullanır. Bu, yukarıdaki örnekteki 47 dakikalık sürenin tamamında alttaki tablolarda Shared Locks (veya Update Locks) oluşturur. Tersine, özyinelemeli CTE bir SELECT ifadesidir. Read Committed Snapshot Isolation (RCSI) veya Snapshot Isolation altında, kilitler tutmadan veri tutarlı bir tarihsel görüşünü okur, sürüm deposu kullanarak. Optimizer genellikle özyinelemeli üye için Index Seek işlemleriyle birlikte Nested Loop Joins'u tercih eder, bu da onu CURSOR yaklaşımlarına göre O(n log n) yapar.
Özyinelemeli bir CTE ile Nested Sets Model arasındaki fark nedir ve hangisini hangi durumlarda seçersiniz?
Adaylar sıklıkla dolaşım yöntemlerini depolama modelleriyle karıştırır. Özyinelemeli CTE, Adjacency Lists (ebeveyn_id yabancı anahtarları) üzerinde çalışan bir sorgu zamanında dolaşım tekniğidir. Nested Sets Model (sol/sağ değerler) ağaç dolaşım yollarını önceden hesaplayan bir depolama tasarım modelidir. Yazma yoğun iş yüklerinde (sık yöneticileri değiştirme) özyinelemeli CTE'ler üst sıradadır çünkü tek bir ebeveyn_id güncelleme O(1) iken, iç içe setler, taşınan düğümün sağındaki tüm sağ değerleri güncelleyerek O(n) güncellemeleri gerektirir. Okuma yoğun, statik hiyerarşiler (ayda bir değişen organizasyon şemaları) için ise iç içe setler O(1) alt ağaç alma (WHERE left BETWEEN parent.left AND parent.right) sağlar, özyinelemeli CTE'ler için ise O(n) gerektirir. Hibrit bir yaklaşım, Closure Tables (tüm atasoy ve soy bağı çiftlerini saklayan ayrı bir tablo) kullanır, bu da hem dolaşım hem de tüm çocukları bulma için O(1) sağlar, ancak O(n²) saklama maliyeti ve daha karmaşık bir bakım gerektirir. Seçim, okuma/yazma oranına bağlıdır: yazmalar işlemlerin %5'inden fazla olduğunda özyinelemeli CTE'leri, çoğunlukla statik ağaçlar için iç içe setler veya closure tabloları kullanın.