Indeks kullanımını anlamak için önce B*Tree yapısı hakkında bilgi sahibi olmakta fayda var.
B*Tree yapısında 3 farklı seviyeden bahsedilir.Bunların ilki en tepede duran “Root”tur.En altta da “leaf” denilen seviye ve bu ikisi arasında da “branch” (lar) bulunur.Örneğin “root” değerimiz 50 olsun.Bundan küçükleri sola , büyükleri sağa , branch tada boyle bir ayrım yaptıgımzı düşünerek yeniden bir dallanma gerçekleştirdiğimizde (leaf) , kabaca bir B Tree oluşturmuş oluruz.(Bu yapının etkili kullanımı için dengeli bir yapıda tutulması gerekmektedir.Yani veriler bir tarafa dogru yığılma yapmamalıdır.)
En etkili avantajı ağaç üzerinde milyonlarca kayıt olsa bile en fazla 2 ya da 3 I/O ile bir kayda ulaşmanın mümkün olmasıdır.(Dezavantaj ise bir düğümün silinmesi ya da değiştirilmesi durumunda ağacın yeniden organize edilmesi ihtiyacının olmasıdır.)
Oracle acısından bu ağacın önemine gelecek olursak , Oracle indeks yapısını bu ağaç yapısı üzerine oturtmuştur.Yani bir kolon üzerinde bir indeks oluşturduğunuzda bu indeks (kolon değeri) ve o verinin bulundugu satır bilgisi(rowid) bu ağaçta uygun yere yerleştirilir.(ROWID,verinin fiziksel adresidir).Leaf seviyesinde ya tek bir değer olur ya da bir değer aralığı bilgisi bulunur.Ama hepsi sıralıdır.Leaf seviyeler birbirine linkli liste mantığına göre bağlıdır.
Örneğin T tablomuzda “id” kolonu üzerinde “index” oldugunu varsayarsak indeks oluşturmak için aşağıdaki gibi yazabiliriz :
SQL > CREATE INDEX t_id_idx ON t(id);
Peki bu aşamada neler yaşanır? “id” kolonunun değerleri artan sırada dizilir.Degeri ve tablodaki satır adresi(rowid) bilgisi saklanır, bu şekilde indeksimiz oluşturulmuş olur.Aşağıdaki sorguya bakalım :
SQL > select * from T where id = 12345
sorgusunda normal şartlarda indeks tarama (index scan) yapılır, önce “id” değeri indeksten bulunur ve satırının rowid bilgisine ulaşılır ve rowid bilgisi ile tabloya nokta atışı yapılarak veriler getirilir. “….where id between 200 and 300…” gibi bir sorguda “leaf” ler üzerinde gezilerek aralık (range) bilgisine ulaşılır.B Tree indeks yapısında tekil(unique) olmayan bir indeks değeri yoktur.
BİTMAP İNDEX
Özellikle DWH(DataWare House) uygulamalarına uygun olan bu indeks türü çok sık tekrar eden kolon bilgileri için ilgili bilginin olmasına(1) ya da olmamasına(0) dayanır.Cinsiyet, bölge bilgisi vs.. gibi birkaç farklı değer içerecek alanlar bitmap indeksler için uygundur.
Bilinen indeks mantığında indeks ile veriye ait ROWID bilgisi (satırın tablodaki yerini gösteren bilgi) bir yerde tutulur ve bu bilgiye göre asıl veriye ulaşılır.Bitmap indekslerde anahtar değer ile birlikte bu değeri taşıyıp taşımadıgını gösteren bir bitmap listesi tutulur(0s,1s).Örneğin aşağıdaki gibi bir CUSTOMER tablomuz olsun.
| CUSTOMER # | MARITAL_ STATUS | REGION | GENDER |
| 101 | single | east | male |
| 102 | married | central | female |
| 103 | married | west | female |
| 104 | divorced | west | male |
| 105 | single | central | female |
| 106 | married | central | female |
Görüldüğü üzere MARITAL_STATUS, REGION ve GENDER alanları sık tekrarlanan değerler almakta.( low-cardinality columns).Bunlar için bitmap indeksler oluşturduğumuzu düşünelim.Örneğin “region” alanına göz atalım :
| REGION=’east’ | REGION=’central’ | REGION=’west’ |
| 1 | 0 | 0 |
| 0 | 1 | 0 |
| 0 | 0 | 1 |
| 0 | 0 | 1 |
| 0 | 1 | 0 |
| 0 | 1 | 0 |
“east” değeri sadece 1.satırda, “central” değeri 2,5 ve 6 da…. oldugundan bulundugu yerlerde “1” bit değeri ile ifade edilmiştir.Mesela ”central ve west bölgelerinde yasayan evli müşteriler” gibi bir sorgu şöyle çalışacaktır.
SQL >SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = ‘married’ AND REGION IN (’central’,’west’);
Bitmap indeks OLTP sistemler için tercih edilmemelidir.Çok sık değişiklik ve yeni veri girişi gören bu sistemlerde bitmap indeksin yeniden düzenlenmesi uzun zaman lacağından, performansı olumsuz etkilememektedir. Özellikle eşitlik,”IN” ile bir liste içersinden seçimli sorgular ve “kaç tane” gibi hesap içeren sorgularda (statik tablolar için(DWH)) uygundur.
”Concatenated Index” dediğimiz birleşik indekler gibi indeksten faydalanmak için kolon adlarını belli bir sırada yazmamıza da gerek yoktur, herhangibir sırada yazılabilirler.NULL değerde içerebilen bitmap index tipi , partition tablolarda da kullanılabilir.Ancak “global” değilde “local index” olarak tanımlanmalıdırlar.
Bütün bu avantajlarının yanında dezavantajları da bulunmaktadır.Örneğin bit seviyesinde bir lock mekanizması yapılamadığından, yapılan bir DML işleminde çok fazla sayıda satır “lock” olabilir, deadlock durumları oluşabilir.
SQL > CREATE BITMAP INDEX orders_region_id_idx
ON orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;
INDEX IFADELERINI DEĞİŞTİRMEK İSTERSEKTE
SQL > ALTER INDEX employees_last_name_idx
STORAGE(NEXT 400K
MAXEXTENTS 100);
SQL > ALTER INDEX orders_region_id_idx
ALLOCATE EXTENT (SIZE 200K
DATAFILE ‘/DISK6/indx01.dbf’);
SQL > ALTER INDEX orders_id_idx
DEALLOCATE UNUSED;
SQL > ALTER INDEX orders_region_id_idx REBUILD
TABLESPACE indx02;
Yukarıdaki sorguyu çalıştırdığımızda eski index silinip yenisi oluşturulmaktadır.Böylece index yeniden oluşturulduğu için performans artacaktır.
Aşağıdaki sorgu ile ise normalde rebuilde edilen indexler tablolarda lock oluştururdu,bu yöntemle lock minimuma indirilmiştir.
SQL > ALTER INDEX orders_id_idx REBUILD ONLINE;
SQL > ALTER INDEX orders_id_idx COALESCE;
İNDEX DOĞRULUK KONTROLÜ
SQL > ANALYZE INDEX orders_region_id_idx
VALIDATE STRUCTURE;
SQL > SELECT blocks, pct_used, distinct_keys
lf_rows, del_lf_rows
FROM index_stats;
BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS
—— ——— ——– ————
25 11 14 0
1 row selected.
Burada dikkat etmemiz gereken DEL_LF_ROWS’un LF_ROWS’e oranı % 30 u geçtiği zaman index analiz edilmeli ve yeniden kontrol edilip oluşturulmalıdır.
SQL > DROP INDEX hr.deptartments_name_idx;
İndex kullanımının nasıl olduğunu gözlemelemek istiyorsak,
Başlangıçta,
SQL > ALTER INDEX summit.orders_id_idx
MONITORING USAGE;
Daha sonra V$OBJECT_USAGE’den aşağıdaki kolon bilgilerini alabiliriz.
INDEX_NAME: İndex İsmi
TABLE_NAME: Tablo İsmi
MONITORING: Monitör durumu ON / OFF
USED: YES / NO Monitoring boyunca index’in kullanılıp kullanılmadığı
START_MONITORING: Monitoring başlamış
END_MONITORING: Monitoring bitmiş
Monitoring’i tamamlamak içinde
SQL > ALTER INDEX summit.orders_id_idx
NOMONITORING USAGE;
İndexler compress edilerek yani skıştırılarak üzerlerinde yeteri kadar yer açılabilir.
SQL> create index emp_last_name_idx
on employees (last_name, first_name)
compress;
SQL> alter index emp_last_name_idx
rebuild compress;
DBA_INDEXES,DBA_IND_COLUMNS,DBA_IND_EXPRESSIONS,V$OBJECT_USAGE viewları ile indexler hakkında detaylı bilgilere ulaşılabilinir.
Şimdi indexlerle ilgili önemli bir kaç soruyu cevaplayacak olursak,
1 ) Indeksler view’larda kullanılabilir mi? Bu sorunun cevabı aslında çokta zor değil.View , bir sorgu cümleciğinden ibarettir.Tablonuzda indeks oluşturduysanız ve view oluştururken kullandığınız sorguda indeksi kullanmasını sağladıysanız view indeks kullanır aksi halde kullanamaz.
2) Indeksler ve NULL : Bitmap indeksler ile clustered indexler hariç B*Tree yapısındaki indeksler NULL barındırmazlar.Bunu şöyle ispat etmek mümkün :create table t2 (x int, str varchar2(15));create unique index idx_t2 on t2(x)declare
begin
insert into t2 values(1,’bir’);
insert into t2 values(2,’iki’);
insert into t2 values(3,’uc’);
insert into t2 values(null,’bos’);
commit;
end;
declare
sqlStr varchar2(500);
begin
sqlStr := ‘analyze index idx_t2 validate structure’;
execute immediate sqlStr;
end;
select name, lf_rows from index_stats
NAME LF_ROWS
IDX_T2 3
Görüldüğü üzere T2 tablosunun (x) kolonuna unique indeks uyguladık, 4 satır ekledik ama kontrol ettiğimizde 4 değil 3 satırın indeksli oldugunu gördük.(x) kolonu NULL değeri aldığı için indekslenmemiştir.
Yine T2 tablosundan “x” kolonu için sorgu yapalım :
select * from t2 where x = 1 sorgusunda “index unique scan” ile 1 satir gelirken
select * from t2 where x is null sorgusunda “table Access full” ile 1 satir gelmiştir.Yani indeks oluşturduğumuz “x” kolonu için NULL sorgusu yaptıgımızda indeks kullanılmamıştır.Bunun sebebi yukarıda da değindiğimiz indekslerin NULL değer içermemesidir.
2 ya da daha fazla kolon üzerinde indeks oluşturdugumuz durumlarda da kolonlardan en az birinin NOT NULL olup değer içermesi gerekir ki indeks kullanılabilsin.NOT : Özellikle unique indeks oluşturmak istediğimizde “NOT NULL” kısıtı koymak indeksi verimli kullanmamızı sağlayacaktır.
3) Indeksler ve Foreign key : Foreign key’in indekslenip indekslenmemesi konusu aslında tamamen tasarımınız ile ilgilidir.Foreign key indekslenmediğinde bizleri bekleyen en büyük problem belkide “deadlock” oluşmasıdır.Peki bu nasıl olur?Bunu anlamak için önce şu bilgileri tekrar edelim :“child table” foreign key barındıran tablodur, “parent table” ise foreign key’in gösterdiği alanın bulundugu tablodur.
Parent tablonun primary key alanında yapılacak bir update ya da parent tablodan bir satırın silinmesi child tabloda bir “table lock” oluşmasına neden olacaktır.Bu durumda da child tablo üzerinde hiç bir şekilde DML işlemi yapılmasına izin verilmeyecektir.Bu da “deadlock” probleminin oluşmasına davetiye çıkarır.Indekslenmemiş foreign key’ler aşağıdaki durumlarda da probleme yol açar :
i) Child tablo da “ON DELETE CASCADE” özelliği olsun ve foreign key’de indeks bulunmasın.Bu durumda parent tabloda bir verinin silinmesi durumunda child tabloda bir “full table scan” yapılmasına sebeb olunur.
ii) Parent tablodan child tabloya dogru olan sorgularda.EMP / DEPT örneği verilebilir.EMP child table, DEPT ise parent tablodur ve örnegin depname = ‘XXX’ olan çalışanları getir gibi bir sorguda sıkıntı yaşayabilirsiniz :
Select * from emp, dept
where emp.deptno = dept.deptno
and dept.depname = ‘XXX’Peki tersi durumlar, yani foreign key değerinin indekslenmesine gerek olmayan durumlar neler olabilir ? :
a) Parent tablodan bir veri silinmeyeceğinde
b) Parent tablonun primary/unique key değeri update edilmeyeceğinde
c) Parent tablo ile child tablo foreign key değeri üzerinden sorgulanmayacağında.Bu şartlarda foreign key indeklemeye gerek yoktur bu sayede DML işlemlerimizde gereksiz yere indekslenmeden dolayı bir yavaşlamaya sebeb olmayacaktır.
Durum 1 : T tablomuzda “x” ve “y” kolonlarımız olsun ve “idx_t(x,y)” şeklinde de bir indeksimiz oldugunu düşünelim.( concatenated index).Bu index ‘i kullanabilmek için “where” koşulundan sonra bu kolonları :
“….where x = :X and y = :Y” şeklinde ya da “…where x = :X” şeklinde , indeksin kullanılmasını sağlamış oluruz.
”…where y=:Y” durumunda ise indeks kullanılmayacaktır.Indeksli kolonlar T(x, y, z) ise indekten faydalanmak için “where” koşulundan sonra aşağıdaki kolon sırası takip edilirse indeks kullanılır :
(x, y, z)
(x,y)
(x)
(x,z), (y,z) gibi kombinasyonlarda indeks kullanılmaz.(Cost Based Optimizer kullanıldıgını düşünüyoruz)
Durum 2 : T tablomuzda “x” kolonu üzerinde indeks oldugu düşünelim.
select count(*) from T , işleminde indeks üzerinden gitmesini beklerken FTS yapmış oldugunu görebiliriz.Bunun muhtemel sebebi indeks oluşturulan “x” kolonu “NULL” içermesi olabilir.NULL içeren kolon değerleri için indeks oluşturulmayacağını görmüştük.Bu durumda Oracle mantıklı olan FTS yapmayı tercih edecektir.
Durum 3 : select * from T where func(indexed_col) = value , gibi bir durumda indeks kullanılamaz.Indeks oluşturulan kolonlar için fonksiyon kullanıldıgında indeks işe yaramaz.Çünkü fonksiyonun değerine değil , fonksiyona parametre olan kolon üzerinde indeks vardır.Eğer buna ihtiyaç var ise “function-based index” kullnılmasında fayda var.
Durum 4 : Bir karakter kolonu indekslediğimizi düşünelim.Aşağıdaki gibi bir sorguda :
select * from T where indexed_col = 5 , indeks kullanılmayacaktır.Çünkü bu sorgu select * from T where to_number(indexed_col) = 5, ile aynıdır.Bu durumuda (Durum 3)’ te açıklamıştık.Bunu genelleyecek olursa implicit olarak yapılan karakter dönüşümlerine engel olunmaz ise indekste kullanılamaz.Benzer bir durum tarih alanları üzerinde indeks oluşturulduğunda karşımıza çıkar.
select * from T where trunc(indexed_date_col) = trunc(sysdate);
indeks TRUNC fonksiyonu sebebi ile yine kullanılamaz.Bu sorgu yerine :
…. indexed_date_col between trunc(sysdate) and trunc(sysdate)plus one mınus one second;
ya da
select * from t where indexed_date_col between trunc(sysdate) and trunc(sysdate) +1 – 1(1*24*60*60)
kullanılmalıdır.Bununla birlikte yukarıdaki 2 yöntemde, değer bir kez hesaplanıp indeks üzerinden gidilirken
“…where trunc(indexed_date_col) = trunc(sysdate);” durumunda ,her satır için trunc(indexed_date_col) hesaplanacaktır.
DURUM 5 : Indeks var ama hala kullanmıyorsam, bunun bir başka sebebi de CBO için gerekli olan istatistikleri toplamamış olmamız olabilir.Bir tablo oluşturulduktan sonra belli zamanlarda ilgili istatistikleri (tablo, kolon, indeksler vs..) toplamamız gerekiyor.Ancak bu sayede CBO en iyi çözümü bulabilir.Indeks buluna bir tabloda indeksli kolon için, tablo küçük iken FTS tercih etmesi doğal olabilir.Ama tablo büyüdükçe indeks range scan ile daha performanlı sorgu yapılabilir.ancak CBO, buna ancak elinde dogru bilgi olursa karar verebilir.
DURUM 6 : “CBO için gerekli istatistikleri de topladım ama indeks yerine hala FTS yapılıyor “ , boyle bir durumda gercekten dogru olan FTS olabilir.Örneğin 1000 kayıtlı bir tablo da indeksli kolon üzerinden 250 kaydı sorgulamak istediğimde , indekse gidip oradan tabloya erişim yapılacağına dogrudan tabloya gidip FTS yapmak daha az maliyetli olacaktır.Bu tablodaki veri sayısı örneğin 100.000’e ulaştıgında aynı sorgu için FTS yapması gariptir, bakılması gerekir.
Indeksler hakkında genel bir kural vardır.”Indeksli alanlar mümkün oldugunca değiştirilmemeli”.Peki neden?
Örneğin B*Tree indeksler , özellikle sorgulama işlemlerinde performanslı iş görmektedir.Ama indeksli kolonu güncelleme ya da yeni bir satır ekleme işlemleri vs. gibi “indeks yapısını bozacak” işlemler tam tersine maliyetli olabilmektedir.Indeksli alan güncellenmesi iyi bir tasarımda hiç karşımıza çıkmayacak ya da düşük ihtimallidir.Ama tabloya yeni veri girilmesi gayet olağandır.Peki bu durumda sistem nasıl davranır ?
Öncelikle uygun “leaf block” tespit edilir(Indeks bilgisinin tutulduğu en alt seviye).Burada tutulan blokta yer var ise en az maliyetle indeksleme yapılmış olur ve yeni indeks bilgisi bu bloğa yerleştirilir.Ama blokta yer kalmamışsa yeni bir blok ayrılır.Mevcut bloktan bir kısım indeks bilgisi de bu yeni bloğa taşınmalıdır.”Leaf Block”’lar arasında “dolaşmayı” sağlayan linkli liste yapısı da bu durumda değişecektir.Görüldüğü üzere yeni bir satır girilmesi bir takım ek işler yapılmasına gerek olmaktadır.Tabi bununda performansa “olumsuz” anlamda “katkısı” olacaktır.Ama veri girmek kadar doğal bir sey olamaz.
Peki en az maliyetle başarmak mümkün mü?Bunlardan kaçınmak bir noktaya kadar mümkün.Örneğin indeksler artan bir sırada oluşturulursa yerleştirme işlemi daha kolay olacaktır.Yine indeks oluştururken PCTFREE parametresini yüksek vermek, bloğa gelecek yeni indeks bilgisinin yeni blok ayrımına gerek kalmasını en aza indirecektir.
SQL > CREATE INDEX hr.employees_last_name_idx
ON hr.employees(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50);
anargodjaev.wordpress.com 