Kategori arşivi: Sql

Veri Ambarında (Kısmen BO’da) Oracle Sql PlSql Pratik Notları

Partition lı işlem tablolarını View ya da Subquery ya da Derived Table üzerinden kullanmamak. Tablo üzerindeki partition lı kolon üzerinde doğrudan filtreleme yapmak. Partition lı kolon üzerinden inner join yapmak. Yukarıdaki kısım Oracle veri tabanı için geçerli. Teradata veri tabanında ise partition lı tablolara view, subquery, derived table üzerinden partition lı kolona filtre koymak partition ın kullanılmamasına sebep olmuyor, yani partition filtrelemesi kullanılmaya devam ediyor ve yüksek performans korunuyor.

Özetlenmiş tablolar (aggregated table) kullanmaya özen göstermek.

Çoğunlukla (kesin kural değil) index kullanmamak; çünkü veri ambarı işlemleri çoğunlukla kayıtlar üzerinde toplu (bulk) işlemler yapmak üzere sorgulanır, bir ya da birkaç kayıt üzerinde işlem yapmak için değil.

Mümkün mertebe join ifadelerinde outer join yerine inner join i tercih etmeye çalışmak, veri ambarı mimarisini buna göre kurmak.

Sorgu senaryosuna bağlı olarak gerekirse With As yapısı kullanmayı tercih etmek.

Pivot ve Unpivot özelliklerini kullanmak.

Analitik fonksiyonları kullanmak. (row_number, lead, lag)

Connect by ifadesini gerektiğinde kullanmak. (connect by prior, connect by level)

Veri validasyonu için düzenli ifadeleri kullanmak. (regular expressions, reg_exp)

PlSql işlerinde cursor tercih etmemek. (Cursor kullanmak kayıtlar üzerinde tek tek işlem yapmak anlamına gelir.)

PlSql ile toplu işlem yapma sorgularında execute immediate kullanmak. (Veri ambarında PlSql kodu ile işlem yaparken create drop ile geçici tablolar üzerinde çalışmak istenildiğinde kodun derlenebilmesi için execute immediate ile obje bağımlılıkları yok edilir. Execute immediate kullanıldığında çalıştırılacak sorgu belirsizdir ve derleme yapılırken obje (tablo, view, vs.) bağımlılıkları hesaplanmadan derleme yapılır.)

oracle nested loop lardan kaçınmak için use_hash ipucu

Veri ambarında çok sayıda kayıt içeren tabloları join lediğimizde çeşitli sebeplerden dolayı (tabloların istatistiğinin henüz alınmamış olması, optimizer ın çalıştırma planını [execution plan] değiştirmek için, vs.) çalıştırma planında [execution plan] nested loop oluşabilir. Bu durum sorgu performansının önemli ölçüde düşmesine sebep oluyorsa Oracle çalıştırma planına müdahale etmek için use_hash ipucu (use_hash hint, hash join) kullanılabilir.

Düz/Normal sorgu:

select t1.*, t2.*
from t1, t2
where t1.b = t2.b

Sorguyu paralel çalıştırmak için:

select /*+ parallel(t1,4) parallel(t2, 4) */
t1.*, t2.*
from t1, t2
where t1.b = t2.b

Sorguyu hem hash join ile hem de paralel çalıştırmak için

select /*+ use_hash(t1,t2) parallel(t1,4) parallel(t2, 4) */
t1.*, t2.*
from t1, t2
where t1.b = t2.b

Oracle With As Kullanımı

Oracle Sql İyileştirme / En İyi Pratik (Oracle Sql Tunning / Best Practice)

Oracle Sql ile çalışırken örnek şöyle bir ihtiyacımız olsun:

t_islem (islem_no, islem_tarihi, islem_tutari, islem_tipi_no)
t_islem_tipi (islem_tipi_no, islem_tipi_tanim, islem_tipi_olusturulma_tarihi)

Yukarıdaki iki tablo gibi tablolarımız olsun, t_islem tablosu da günlük ya da aylık partion lı (içindeki veriye göre parçalı, bölünmüş) çok sayıda kayıt içeren bir tablo olsun. Amacımız da dönemlik toplamların yanına yıllık toplamları getirmek olsun.

Geleneksel çözüm:

select t1.yil_ay,
t1.islem_tipi_tanim,
t1.tutar as yil_ay_tutar,
t2.tutar as yil_tutar
from
(
    select to_number(to_char(t_islem.islem_tarihi,'YYYYMM')) as yil_ay,
    t_islem_tipi.islem_tipi_tanim,
    sum(islem_tutari) as tutar
    from t_islem,
    t_islem_tipi
    where t_islem.islem_tipi_no = t_islem_tipi.islem_tipi_no
    group by to_number(to_char(t_islem.islem_tarihi,'YYYYMM')),
    t_islem_tipi.islem_tipi_tanim
) t1,
(
    select to_number(to_char(t_islem.islem_tarihi,'YYYY')) as yil,
    t_islem_tipi.islem_tipi_tanim,
    sum(islem_tutari) as tutar
    from t_islem,
    t_islem_tipi
    where t_islem.islem_tipi_no = t_islem_tipi.islem_tipi_no
    group by to_number(to_char(t_islem.islem_tarihi,'YYYY')),
    t_islem_tipi.islem_tipi_tanim
) t2
where trunc(t1.yil_ay/100) = t2.yil
and t1.islem_tipi_tanim = t2.islem_tipi_tanim

With As ile geçici tablo (kaynak yeterliliğine göre çoğunlukla bellekte) oluşturularak ve daha az kod ile:

with t1 as (
    select to_number(to_char(t_islem.islem_tarihi,'YYYYMM')) as yil_ay,
    t_islem_tipi.islem_tipi_tanim,
    sum(islem_tutari) as tutar
    from t_islem,
    t_islem_tipi
    where t_islem.islem_tipi_no = t_islem_tipi.islem_tipi_no
    group by to_number(to_char(t_islem.islem_tarihi,'YYYYMM')),
    t_islem_tipi.islem_tipi_tanim
)
select t1.yil_ay,
t1.islem_tipi_tanim,
t1.tutar as yil_ay_tutar,
t2.tutar as yil_tutar
from t1,
(
    select trunc(yil_ay/100) as yil,
    islem_tipi_tanim,
    sum(tutar) as tutar
    from t1
    group by trunc(yil_ay/100),
    islem_tipi_tanim
) t2
where trunc(t1.yil_ay/100) = t2.yil
and t1.islem_tipi_tanim = t2.islem_tipi_tanim

oracle pl-sql nls (national language settings) notlar

Not 1:

Oracle Pl-Sql ile ay isimlerini Türkçe (ya da farklı dillerde) çekebilmek için aşağıdaki gibi oturum parametreleri (session parameters) değiştirilebilir.


select to_char(sysdate,'MONTH') from dual; -- veri tabanı varsayılan parametreleri ile çalışır

alter session set nls_date_language = 'ENGLISH';

select to_char(sysdate,'MONTH') from dual; -- İngilizce ay isimleri döndürür

alter session set nls_date_language = 'TURKISH';

select to_char(sysdate,'MONTH') from dual; -- Türkçe ay isimleri döndürür

İlgili kaynak:
oracle oturum parametreleri yardımcı sql ifadeleri

Not 2:

Oracle Sql ile karakter tipindeki veriyi sayısal değere nokta ve virgül algılamasını değiştirerek çevirebilmek için aşağıdaki gibi NLS_NUMERIC_CHARACTERS gönderilebilir. (Converting [Casting] varchar values to number with different perception [sensitivity].)


select to_number('100,12', '99999D99', 'NLS_NUMERIC_CHARACTERS='',.''') from dual;

select to_number('100.12', '99999D99', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;

Kaynak:
https://stackoverflow.com/questions/24571355/nls-numeric-characters-setting-for-decimal

oracle Türkçe karakter büyük harf küçük harf çevrimi

Oracle veritabanında tutulan Türkçe karakterin olduğu metinleri Türkçe karakterler bozulmadan büyük harfe ya da küçük harfe çevrimi için NLS_UPPER() ve NLS_LOWER() fonksiyonu kullanılır. (NLS: National Language Support)

Örneğin:

select nls_upper('deli','nls_sort=xturkish') from dual; -- sonuç: 'DELİ'

select nls_lower('DELİ','nls_sort=xturkish') from dual; -- sonuç: 'deli'

Kaynak:
NLS_UPPER – Oracle Database SQL Reference

sap bo koşullu dağıtım (conditional publication)

Sap BO ortamında rapor dağıtımı [http://ayhankargin.com/blog/?p=314] yaparken (publication) dağıtım yapılan tüm hedeflere her zaman sorgu sonucu veri gelmiyorsa, ve boş rapor içeriği olunca gönderim yapılması istenmiyorsa aşağıdaki yöntem ile bu sağlanabilir.

Dağıtımı yapılacak rapor A olsun. Dağıtım listesi B olsun. Dağıtımı yapılan A raporu ile dağıtım listesi olan B raporunun birleştirilme (join) alanı da C alanı olsun. Örnek olarak rapor sorguları aşağıdaki gibi olsun.

A rapor sorgusu:

select a.islem_sube, islem_tarih_saat, islem_tutar, islem_aciklama
from ozel_islem_listesi a
where a.tarih between trunc(sysdate-1) and trunc(sysdate)

B rapor sorgusu:

select b.islem_sube, b.sube_eposta
from islem_sube b

C alanı:

ozel_islem_listesi.islem_sube = islem_sube.islem_sube

Böyle bir dağıtımda bir şube için A raporundan her zaman veri gelmeyebilir. Amacımız da eğer A raporunda bir şube için veri gelmiyorsa B rapor dağıtımı listesinde de bu şubenin gelmemesi.

Bunun için B rapor sorgusunu aşağıdaki gibi bir hale getirirsek A raporunda gelmeyen sube B raporunda da gelmez.

B rapor sorgusu değiştirilmiş hali:

select b.islem_sube, b.sube_eposta
from islem_sube b
where b.islem_sube in (
    select a.islem_sube
    -- , islem_tarih_saat, islem_tutar, islem_aciklama
    from ozel_islem_listesi a
    where a.tarih between trunc(sysdate-1) and trunc(sysdate)
)

B rapor sorgusu Custom Sql, FreeHand Sql veya Result from Another Query gibi özellikler ile değiştirilebilir.

çoklu bağlantılı platformlar tavsiye (multisource universe recommendation)

Sap Business Objects bir universe ün (unx) birden fazla bağlantı ile oluşturulmasına (kullanılabilmesine) olanak sağlar. Bunun için BI Platformda Data Federation servisinin oluşturulması gerekir. Çoklu bağlanıtılı universe özelliği universe ün farklı veritabanlarından aldığı veriyi Data Federation servisi ile birleştirerek (veritabanı gibi davranarak) raporlara sonuç verisini sağlamasıyla olur. Çoklu bağlantılı universe oluşturulurken bir kere çoklu kaynaklı olduğu belirlenir sonradan tek bağlantılı universe e çevrilemez.

Kişisel tavsiyem Çoklu Bağlantılı (MultiSource) universe özelliğinin zorunlu olunmadıkça kullanılmaması yönünde. Çünkü çoklu bağlantılı platformlar farklı veri kaynaklarından alınan veriyi veritabanı gibi (BI platformun veritabanı görevi yapması performansı çok negatif etkileyen bir faktör, hem işlemci hem de bellek kullanımı açısından) davranarak birleştirmesi gerekir. Özellikle farklı kaynaklardaki objelerin data foundation da join lenmesi durumunda.

Çoklu bağlantılı universe ler yerine veriyi Veri Entegrasyonu (Data Integration), ETL (Extract Transform Load) Araçları ile tek veritabanında birleştirmek ve tek bağlantılı universe ile bu veritabanından raporlamak daha doğru bir çözüm olacaktır. Veri Entegrasyonu araçları farklı veritabanı yönetim sistemlerine bağlanıp veri aktarımı yapabilmektedir. Bu sayede BI Platforma veri tabanı yükü bindirilmemiş olur ve sorgulama dilinin en hızlı çalıştığı ortamda veri alınarak performanslı raporlama yapılmış olur.

Not 1: tecrübelerimde çoklu kaynaklı universe ler ile ilgili çeşitli hatalarla (bug) karşılaştığımı söyleyebilirim.

Not 2: çoklu bağlantılı universe ler ile çalışırken tekli bağlantılı universe lere göre daha yavaş çalışırlar sebebi MultiSource universe lerin üzerinde geliştirme yapılırken işlemlerin yerel makinada değil sunucuda (bi platform) yapılıyor olmasıdır.

Not 2 Kaynak: IDT client tool slows dramatically in business layer