CTE, Alt Sorgu ve Geçici Tablo Karşılaştırması

Bir sorgunun ara bir sonuca ihtiyacı olduğunda üç yaygın aracınız vardır: bir alt sorgu, bir CTE ve bir geçici tablo. Mülakat yapanlar bunları karşılaştırmanızı ister; çünkü seçiminiz, fiziksel olarak oluşturma ve sorgu iyileştiricinin davranışı hakkındaki anlayışınızı gösterir.

Bu ders, baskı altında aktarabileceğiniz bir karar çerçevesi oluşturur.

Alt sorgu, genellikle FROM, WHERE veya SELECT içinde başka bir sorgunun içine yerleştirilen satır içi bir sorgudur. Aynı ifadenin parçasıdır ve sorgu iyileştirici onu tek bir birim olarak görür.

• Ad vermek gerekmez; türetilmiş tablolar için takma ad gerekir.
• Sorgu iyileştirici onu dış sorguyla birleştirmekte serbesttir.
• Derin biçimde iç içe yerleştirildiğinde uzar ve okunması zorlaşır.

CTE, kapsamı tek bir ifadeyle sınırlı olan WITH bloğundaki adlandırılmış bir alt sorgudur. Derin biçimde iç içe yerleştirilmiş bir alt sorgudan daha okunaklıdır ve kendisine birden çok kez başvurulabilir.

• Adlandırıldığı için amacı belgelenmiş olur.
• Aynı ifade içinde birden fazla kez kullanılabilir.
• Kapsamı yine tek bir ifadeyle sınırlıdır ve sonra ortadan kalkar.

Geçici tablo, oturum boyunca (veya işlem boyunca) yaşayan gerçek ve fiziksel bir tablodur. Bir ifadeyle doldurur, sonraki ayrı ifadelerde sorgularsınız.

• Oturumdaki birden çok ifade boyunca varlığını sürdürür.
• Üzerine dizin eklenebilir ve istatistikleri toplanabilir.
• Disk giriş/çıkışı ve açıkça temizleme işlemi maliyeti getirir.

Mülakat yapanların üzerinde durduğu temel kavram fiziksel olarak oluşturmadır: ara sonucun fiziksel olarak bir yere yazılıp yazılmadığı.

• Alt sorgular ve CTE'ler genellikle fiziksel olarak oluşturulmaz; sorgu iyileştirici çoğu zaman bunları ana sorguya birleştirir.
• Geçici tablo her zaman depolama alanında fiziksel olarak oluşturulur.
• Bazı veritabanları, ipuçlarıyla CTE'nin fiziksel olarak oluşturulmasını zorunlu kılmanıza veya engellemenize izin verir.

Geçmişte PostgreSQL, her CTE'yi bir optimizasyon bariyeri olarak ele alır; onu fiziksel olarak oluşturur ve koşulun alt sorguya itilmesini engellerdi. Postgres 12'den bu yana, bir kez başvurulan basit ve özyinelemeli olmayan CTE'ler varsayılan olarak ana sorguya birleştirilir; MATERIALIZED ve NOT MATERIALIZED ipuçlarıyla bu davranışı değiştirebilirsiniz.

Bu ayrıntıdan söz etmeniz, kıdemli düzeyde olduğunuzu güçlü biçimde gösterir.

Aynı ara sonuca bir ifade içinde birkaç kez başvuruyorsanız, bir CTE'yi kullanmak alt sorguyu tekrarlamaktan daha düzenli olabilir. Ancak dikkatli olun: satır içine alınan bir CTE her başvuruda yeniden hesaplanabilir.

Yeniden hesaplama pahalıysa fiziksel olarak oluşturmayı zorunlu kılmak (veya geçici tablo kullanmak), işi iki kez yapmanızı önler.

CTE'ler ve alt sorgular yalnızca bir ifade boyunca yaşar. Aynı sonuca birkaç ayrı sorguda ihtiyacınız varsa doğru araç geçici tablodur.

Yaygın bir örnek, bir hazırlama kümesini bir kez oluşturduğunuz ve ardından ona karşı birkaç analiz çalıştırdığınız çok adımlı bir ETL süreci veya rapordur. Geçici tabloya dizin eklemek, sonraki her sorguyu hızlandırabilir.

Yalnızca geçici tablo dizin ve güncel istatistik taşıyabilir. Birçok kez birleştirilen devasa bir ara sonuç kümesi için bu belirleyici olabilir.

• CTE/alt sorgu: sorgu iyileştirici, temel tablolardan tahminlerde bulunur.
• Geçici tablo: Üzerinde ANALYZE çalıştırabilir ve sonraki birleştirmelerinize göre ayarlanmış dizinler ekleyebilirsiniz.

Bu nedenle büyük ve yoğun biçimde yeniden kullanılan sonuçlarda, ek adımlara rağmen performans açısından geçici tablo daha iyi olabilir.

Mülakat için net bir cevap:

• Alt sorgu: tek seferlik, az iç içe ve okunabilirliği yeterli olan durumlar.
• CTE: okunabilirliği artırdığı veya aynı ifade içinde birkaç kez başvurduğunuz durumlar.
• Geçici tablo: ifadeler arasında yeniden kullanılan, çok büyük olan ya da dizinlere/istatistiklere ihtiyaç duyulan durumlar.

Netlik için varsayılan olarak CTE kullanın; fiziksel olarak oluşturma veya ifadeler arasında yeniden kullanım gerçekten fayda sağladığında geçici tabloya başvurun.

'CTE'ler her zaman daha yavaştır' gibi kesin yargılardan kaçının. Bunun yerine şöyle söyleyin: CTE'ler ve alt sorgular genellikle ana sorguya birleştirilir; dolayısıyla mesele okunabilirliktir. Geçici tablo fiziksel olarak depolanır ve büyük bir sonucu ifadeler arasında yeniden kullanacağımda veya bir dizine ihtiyaç duyduğumda buna değer.

Bu davranışın veritabanı motoruna özgü olduğunu (Postgres'te ayrıca sürüme göre değiştiğini) kabul etmeniz, konuya gerçekten hakim olduğunuzu gösterir.

Geçici tablonun açıkça daha iyi bir seçim olduğu senaryoyu belirleyin.

Seçim, fiziksel olarak oluşturma ve kapsam kavramlarına dayanır.

• Alt sorgular ve CTE'ler: genellikle ana sorguya birleştirilir, tek bir ifadeyle sınırlıdır ve okunabilirlik için seçilir.
• CTE'ler adlandırma ve ifade içinde yeniden kullanım olanağı sağlar.
• Geçici tablolar: her zaman fiziksel olarak oluşturulur, ifadeler arasında varlığını sürdürür ve dizinlenebilir.
• Postgres 12 ve sonraki sürümler basit CTE'leri ana sorguya birleştirir; bunu denetlemek için MATERIALIZED ipuçlarını kullanın.

Sırada: karmaşık, iç içe bir sorguyu temiz CTE'lere dönüştürmek var.