Kategori arşivi: Oracle

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

oracle sql ile bir satırı sütundaki değer kadar çoklamak

İhtiyaç:

t tablosu
———

s1, s2
1, ‘a’
2, ‘a,b,c’
3, ‘a,b’
4, ‘c,b’

Tablosunu yukarıdaki durumdan aşağıdaki duruma çevirmek. (s2 sütununda virgül ile ayrılmış değerleri satırlara çevirmek. Bir diğer kriter de bu tablonun milyon satır olduğunda da performanslı çalışabilmesi. Bir satırın kaça parçalanacağı (kaç satıra çevirileceği) virgüller sayılarak hesaplanabilir.)

sorgu sonucu amaçlanan
———————-

s1, s2
1, ‘a’
2, ‘a’
2, ‘b’
2, ‘c’
3, ‘a’
3, ‘b’
4, ‘c’
4, ‘b’

Oracle özyinelemeli (recursive) sql sorgu ifadesi ile çözüm:

WITH split (s1, s2, s2_count, split_level) AS (

    select t1.s1, t1.s2, regexp_count(t1.s2,',') as s2_count, 1 as split_level
    from t t1

    union all

    select split.s1, split.s2, split.s2_count, split.split_level+1 as split_level
    from split
    where split_level < s2_count

)

SELECT s1, s2, s2_count, split_level,

case when s2_count > 1 and s2_count <> split_level then
      substr( s2, instr(s2,',',1,split_level), (instr(s2,',',1,split_level+1)-instr(s2,',',1,split_level)-2) )
     when s2_count > 1 and re_cnt = lvl then
       substr( s2, instr(s2,',',1,split_level), (length(s2)-instr(s2,',',1,split_level)+1) )
     else s2
end as new_s2

FROM split
ORDER BY 1,2,3,4,5

Kaynak: https://stackoverflow.com/questions/41589163/oracle-split-single-row-into-multiple-rows-based-on-column-value

tablo satır sayıları arşivi oracle plsql

Periyodik olarak tablolardaki satır sayılarını saklamak için kullanılabilecek Oracle stored procedure.


create table sample_schema.table_counts (
owner_schema varchar2(30),
table_name varchar2(30),
row_count number
);

create or replace procedure sample_schema.table_counts_calculator(p_schema varchar2)
is

v_user varchar2(30);
v_schema varchar2(30);
v_now date;
v_cnt number;

cursor c_tables(cp_schema varchar2)
is
select owner, table_name
from all_tables
where owner = cp_schema
order by 1;

begin

select user into v_user from dual;

v_schema := upper(p_schema);

select sysdate into v_now from dual;

for tbl in c_tables(v_schema)
loop

    execute immediate '
    select /*+parallel(8)*/ count(*) cnt
    from '||tbl.owner||'.'||tbl.table_name||'
    ' into v_cnt;

    execute immediate '
    insert into '||v_user||'.table_counts values ( '''||tbl.owner||''','''||tbl.table_name||''', '||v_cnt||' )
    ';

end loop;

commit;

end;
/