Oracle SQL ile Güvenli Sayı Dönüşümü

Oracle'da metin sütunundan sayısal sonuç üretirken ilk karar TO_NUMBER kullanmak değil, hangi girdilerin geçerli sayı kabul edildiğini tanımlamaktır. NULL, ondalık ve grup ayırıcıları, NLS ayarları ve hatalı metnin raporlanma biçimi aynı veri sözleşmesinin parçasıdır.

Oracle tabanlı veri işleme sistemlerinde metinsel alanlardan sayısal değer üretirken asıl sorun çoğu zaman TO_NUMBER çağrısı değil, kirli verinin hangi kuralla geçerli kabul edildiğidir. Büyük veri kümelerinde tek bir bozuk değerin raporu kesmesi veya hatalı değerin sessizce sıfıra dönüşmesi operasyonel olarak farklı sonuçlar doğurduğu için burada dönüşüm, doğrulama ve hata görünürlüğünü birlikte ele alıyorum.

Girdiyi Önce Normalleştirmek

Kaynak değerler aşağıdaki gibi olabilir:

adet:12
adet: 12
12
12,5
1.250
bilinmiyor

Ön ek temizliği, büyük-küçük harf, baştaki ve sondaki boşluklar açık biçimde ele alınmalıdır. Kaynak sözleşmesi yalnız adet: biçimini garanti ediyorsa sade CASE yeterlidir. İnsan girişi veya eski sistem verisi söz konusuysa TRIM, kontrollü büyük-küçük harf dönüşümü ve doğrulanmış bir desen gerekebilir.

CASE
    WHEN LOWER(SUBSTR(TRIM(aciklama), 1, 5)) = 'adet:'
    THEN TRIM(SUBSTR(TRIM(aciklama), 6))
    ELSE TRIM(aciklama)
END

Bu normalizasyonun hangi biçimleri kabul ettiği test verileriyle belgelenmelidir. Gereğinden geniş regex kullanmak hatalı değerleri geçerli hale getirebilir.

Dönüşüm Hatasını Yönetmek

Oracle'ın desteklenen sürümlerinde DEFAULT ... ON CONVERSION ERROR, sayısal dönüşüm başarısız olduğunda varsayılan değer döndürmeyi sağlar:

TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR)

Bu yapı, value ifadesinin hesaplanması sırasında oluşan bütün hataları yakalayan genel bir istisna mekanizması değildir. Yalnız hedef tipe dönüşüm hatasını ele alır.

Geçersiz değeri doğrudan sıfıra çevirmek raporu kesintisiz üretir; ancak bozuk veriyi gerçek sıfırla aynı sınıfa taşır. Çoğu denetlenebilir sistemde geçersiz değerin NULL olarak ayrılması ve hata sayısının ayrıca raporlanması daha güvenlidir.

NULL ve Aggregate Semantiği

Oracle boş karakter dizisini karakter bağlamında NULL olarak ele alır. TO_NUMBER(NULL) hata üretmez; sonuç yine NULL olur. SUM ise NULL değerleri hesaba katmaz. Hiç geçerli değer yoksa sonuç NULL olabilir.

SELECT
    COALESCE(SUM(TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR)), 0) AS toplam
FROM source_data;

Buradaki COALESCE, yalnız aggregate sonucunun iş kuralı gereği sıfır olması isteniyorsa kullanılmalıdır. Bilinmeyen toplam ile gerçek sıfır aynı anlama gelmiyorsa NULL korunmalıdır.

NLS Bağımlılığını Kaldırmak

Açık format modeli verilmezse sayı metni oturumun NLS_NUMERIC_CHARACTERS ayarına göre yorumlanabilir. 1.234 değeri bir oturumda ondalık sayı, başka bir oturumda binlik gruplanmış sayı olabilir. Dönüşüm hata vermeden yanlış sonuç üretebildiği için yalnız DEFAULT ON CONVERSION ERROR yeterli koruma sağlamaz.

Biçim biliniyorsa format ve NLS parametresi sorguda sabitlenmelidir:

TO_NUMBER(
    value DEFAULT NULL ON CONVERSION ERROR,
    'S999999999999D999999',
    q'[NLS_NUMERIC_CHARACTERS = '.']'
)

Format modelinin kabul ettiği işaret, basamak sayısı ve ondalık hassasiyet gerçek veri sözleşmesiyle uyumlu olmalıdır.

VALIDATE_CONVERSION ile Veri Kalitesi

Dönüşümün geçerli olup olmadığı ayrı bir çıktı olarak korunabilir:

SELECT
    SUM(TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR)) AS toplam,
    SUM(
        CASE
            WHEN value IS NOT NULL
             AND VALIDATE_CONVERSION(value AS NUMBER) = 0
            THEN 1
            ELSE 0
        END
    ) AS gecersiz_kayit
FROM normalized_data;

Format modeli kullanılıyorsa doğrulama ve dönüşüm aynı format/NLS sözleşmesini kullanmalıdır. Aksi halde doğrulanan değer başka kuralla dönüştürülebilir.

İş Kuralı Dönüşümden Ayrıdır

Bir metnin NUMBER tipine çevrilebilmesi, iş açısından geçerli olduğu anlamına gelmez. Adet alanında negatif değer, bilimsel gösterim veya kesirli sayı kabul edilmeyebilir. Dönüşümden sonra domain kısıtları uygulanmalıdır:

CASE
    WHEN parsed_value >= 0
     AND parsed_value = TRUNC(parsed_value)
    THEN parsed_value
    ELSE NULL
END

Yuvarlama, veriyi geçerli hale getiren sessiz bir temizlik işlemi olmamalıdır. 12,7 değerinin 13 yapılması yalnız açık bir iş kuralı varsa uygulanmalıdır.

Performans ve İndeks Kullanımı

Sütun üzerinde TRIM, CASE, regex ve TO_NUMBER çalıştırmak her satır için CPU maliyeti üretir ve normal indeksin kullanılmasını engelleyebilir. Sorgu sık çalışıyorsa aşağıdaki seçenekler değerlendirilmelidir:

• Kaynak veriyi ingest sırasında normalize etmek
• Sayısal değeri ayrı bir NUMBER sütununda tutmak
• Sanal sütun ve function-based index kullanmak
• Hatalı kayıtları ayrı kalite kuyruğuna yönlendirmek
• Dönüşümü ortak tablo ifadesinde bir kez hesaplamak

WITH normalized AS (
    SELECT
        TO_NUMBER(clean_value DEFAULT NULL ON CONVERSION ERROR) AS numeric_value
    FROM source_data
)
SELECT SUM(numeric_value)
FROM normalized;

Aynı karmaşık ifadeyi SELECT, WHERE ve ORDER BY içinde tekrar etmekten kaçınılmalıdır.

Denetlenebilir Sonuç

Güvenli sayı dönüşümü, hatayı bastırmak değil semantiği açık hale getirmektir. Üretim sorgusu en az geçerli toplamı, geçersiz kayıt sayısını ve mümkünse örnek hatalı değerleri ayrı gösterebilmelidir. NLS ayarı, kabul edilen biçim, NULL politikası, negatif ve kesirli değer kuralları SQL içinde veya veri sözleşmesinde sabitlenmelidir. Böylece aynı veri farklı oturumlarda farklı sonuca dönüşmez ve veri kalitesi kaybı toplamın içinde görünmez hale gelmez.

VALIDATE_CONVERSION, NULL ve Boşluk Sınır Durumları

VALIDATE_CONVERSION kullanılırken NULL semantiği açık olmalıdır. Oracle, ifade NULL olarak değerlendirildiğinde fonksiyonun 1 döndürdüğünü belgeler. SQL tarafında sıfır uzunluklu karakter dizisi de NULL olarak ele alınır. Buna karşılık yalnız boşluk karakterlerinden oluşan girdiler için davranışı varsaymak yerine hedef Oracle sürümü ve NLS ayarlarıyla açıkça test etmek daha güvenlidir.

Örnek bir regresyon matrisi:

SELECT
    VALIDATE_CONVERSION(NULL AS NUMBER) AS v_null,
    VALIDATE_CONVERSION('' AS NUMBER) AS v_empty,
    VALIDATE_CONVERSION(' ' AS NUMBER) AS v_space,
    VALIDATE_CONVERSION(TRIM(' ') AS NUMBER) AS v_trimmed_space,
    VALIDATE_CONVERSION('0' AS NUMBER) AS v_zero,
    VALIDATE_CONVERSION('12.5' AS NUMBER) AS v_decimal
FROM dual;

Buradaki amaç tek bir çıktı tablosunu ezberlemek değildir. Uygulamanın kabul ettiği sayısal metin dilini, gerçek Oracle sürümü ve NLS politikası altında tanımlamaktır. VALIDATE_CONVERSION, TO_NUMBER, TRIM ve toplama işlemleri aynı veri sözleşmesini izlemelidir.

Daha geniş veri tabanı bağlamı için Oracle Veritabanı ve PL/SQL: Mimari, SQL ve Performans notuna bakılabilir.

Kaynağın Doğruladığı Şey

Bu yazıdaki Oracle davranışlarını üç ayrı sınıfta okumak gerekir. TO_NUMBER, DEFAULT ... ON CONVERSION ERROR ve benzeri sözdizimsel/dönüşüm kuralları Oracle SQL Language Reference tarafından tanımlanır; VALIDATE_CONVERSION ise belirli bir değerin hedef türe dönüştürülebilirliğini sorgulamak için doğrudan belgelenmiş işlevdir. Buna karşılık hangi yaklaşımın belirli bir tabloda daha hızlı olduğu; veri dağılımı, NLS ayarı, indeks yapısı, optimizer sürümü ve sorgu şekline bağlı deneysel bir sorudur.

Bu nedenle yazı, örnek SQL'i evrensel performans reçetesi olarak sunmaz. Üretim ortamında doğrulama için hedef sürümde execution plan, actual row sayıları ve gerçek veri dağılımı birlikte incelenmelidir. Metindeki “güvenli dönüşüm” ifadesi, hatalı metnin kontrolsüz biçimde sorguyu bozmasını önleyen açık dönüşüm sözleşmesini anlatır; bütün veri kalitesi kurallarını otomatik olarak çözmez.

Dönüşüm Güvenliği ile Sorgu Planı Arasındaki Bağ

Sayısal dönüşüm yalnız veri temizliği konusu değildir. Bir kolonda sayı dışı değer bulunma olasılığı varsa dönüşümün hangi sırada uygulandığı, predicate'in hangi satırlara kadar itilebildiği ve optimizer'ın ifadeyi nasıl yeniden düzenlediği aynı sorgunun hem doğruluğunu hem maliyetini değiştirebilir. Bu nedenle güvenli dönüşüm deseni, Sargability, Selectivity ve Database Histogram gibi planlama kavramlarıyla birlikte değerlendirilmelidir.

Üretim sisteminde asıl hedef, hatalı bir satırın tüm sorguyu düşürmesini engellerken indeksi gereksiz yere işlevsiz bırakmamaktır. Sorgu planı değiştiğinde yalnız ortalama süreye bakmak yerine seçilen erişim yolu, cardinality tahmini ve farklı bind değerlerinde planın kararlılığı da kontrol edilmelidir. Oracle tarafındaki bu sınır, Adaptive Cursor Sharing ve Clustering Factor maddeleriyle doğrudan ilişkilidir.

Dönüşümün Optimizer ve İndeks Davranışına Etkisi

Güvenli dönüşüm yalnız hata üretmemekle sınırlı değildir. Predicate içinde kolon üzerinde uygulanan dönüşüm, normal B-tree indeksinin doğrudan kullanılabilirliğini ve selectivity tahminini etkileyebilir. Fonksiyon tabanlı indeks veya sanal kolon gibi seçenekler workload'a göre değerlendirilebilir; ancak önce veri tipinin neden metin tutulduğu ve sorgunun hangi cardinality ile çalıştığı anlaşılmalıdır.

Bind dağılımı, histogram ve cardinality tahminiyle birlikte oluşan plan farklılıklarını Oracle Sorgu Planı Kararlılığı başlığında ayrı ele alıyorum.

Kaynakça

• Oracle Corporation. (2017). Oracle Database SQL Language Reference, 12c Release 2 (12.2). Oracle. URL