İndex konusu uzun bir zamandır yazmayı planladığım birkaç konudan birisi idi. Umarım faydalı olur.

Oracle ‘ da indexler olmazsa olmazlarımızdan dır diyebiliriz. Aslında exdata gibi yeni teknolojilerle tanıştıkca yeni değişimler olsada hala indexsiz bir yaşam düşünemiyoruz. Peki çok klasik bir soruyle başlayalım o zaman ;
Nedir İndex ? Tablo üzerinde yapılan Select, İnsert, Update gibi işlemlerin daha performansla çalışması için oluşturulan nesnelerdir. Bir örnekle açıklayalım, çok büyük bir tablo üzerinde işlem yapıyorsanız ve çalıştığınız data miktarı tablonun toplam data size’ ının %5-6 ından büyük değilse ; böyle bir durumda eğer index kullanmıyorsanız çalıştığınız sorgu tablonun yer aldığı tüm blokları okumaya çalışacaktır. Eğer index kullanırsanız da sadece ilgili blokları okumayacağı çalışacağından sorgunuz daha performanslı çalışması sağlayacaktır.

İndexler genel olarak Select sorguları, Where cümleciği içeren Update sorguları ve yine Where cümleciği içeren Delete sorgularında ciddi performans artışları sağlarlar. İndexler Insert sorgularında ise performance düşürücü bir etkiye sahiptirler.Yine indexsli kolonlar üzerinde yapılacak update – delete işlemleride nispeten yavaş olacaktır.

Database’ de yer alan indexleri sorgulamak için aşağıdaki sorgulardan faydalanabiliriz ;

select table_name, index_name
from dba_indexes

select table_name, index_name, column_name, column_position
from dba_ind_columns
order by table_name, index_name, column_position

İndex Kullanımını Etkileyen Yanlışlıklar

Sorgu yazımındaki kimi hatalardan dolayı indexli kullanılması gerekilen durumlarda index’ in kullanılmadığına şahit olabilirler. Bu tarz durumların neler olabileceğine sırayla bakalım ;

• Eşit Değildir Operatörünün Kullanımı (‘<>’ ve ‘!=’)

İndexler sadece tablo içerisindeki datayı bulmakta kullanılırlar. Dolayısıyla size sorgularınızda tabloda olmayan bir kaydı “eşit değildir” operatörü ile sorgulamak isterseniz oracle index kullanımını iptal edecektir. Oracle’ ın sample scheması içerisindeki tabloları kullanarak bir örnek verelim ; EMP scheması altındaki employees tablosunda emploee_id kolonunda index bulunmaktadır. Eşit operatörü ile aşağıdaki sorguyu kontrol ettiğimizde index kullandığını görüyoruz.

select * from hr.EMPLOYEES
where employee_id <> 5

Plan
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 69 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE HR.EMPLOYEES Cost: 1 Bytes: 69 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.EMP_EMP_ID_PK Cost: 0 Cardinality: 1

Eşit değildir operatörü ile sorgunun execution planına batığımızda sorgunun index kullanmadan full table scan yaptığını görüyoruz.

• Is Null veya Is Not Null Operatörünün Kullanılması ;

Sorgunun Where kısmında is null veya is not null kullanırsanız, oracle index kullanımını engelleyecektir.

select * from hr.EMPLOYEES
where salary is null

Plan
SELECT STATEMENT ALL_ROWS Cost: 3 Bytes: 69 Cardinality: 1
1 TABLE ACCESS FULL TABLE HR.EMPLOYEES Cost: 3 Bytes: 69 Cardinality: 1

• Function Kullanımı ;

Eğer function –based index kullanmıyorsanız, indexli kolonlar üzerinde Where cümleciği içerisinde indexli kolonlar üzerinde function kullanırsanız optimizer index kullanımını engelleyecektir. Sık kullanılan functionlara örnek olarak Trunc, Substr, to_date, to_char ve instr kullanımını örnek olarak verebiliriz.

select * from hr.EMPLOYEES
where trunc(hire_date) = ’21/06/2007′

Plan
SELECT STATEMENT ALL_ROWS Cost: 3 Bytes: 69 Cardinality: 1
1 TABLE ACCESS FULL TABLE HR.EMPLOYEES Cost: 3 Bytes: 69 Cardinality: 1

Function kullanımı kolonun içerisindeki değerin alter edilmesine neden olmaktadırlar. Bu yüzden, kolonlar üzerindeki indexleri kullanmayacaktır.

Bu tarz durumlarda sorguyu sorguyu aşağıdaki gibi değiştirmeniz durumunda index kullanabilirsiniz ;

select * from hr.EMPLOYEES
where hire_date > ‘21.06.2007’
and hire_date < (to_date('21/06/2007') + 0.99) Plan SELECT STATEMENT ALL_ROWS Cost: 2 Bytes: 69 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE HR.EMPLOYEES Cost: 2 Bytes: 69 Cardinality: 1 1 INDEX RANGE SCAN INDEX HR.INDEX_HIRE_DATE_1 Cost: 1 Cardinality: 1 • Farklı Data Type’ larının Compare Edilmesi ;

En zor performans sorunlarından biride, farklı data type’ larının karşılaştırılmasının sonucunu bulmaktır. Oracle farklı data typelarının karşılaştırılmasına kızmamakla birlikte, bu şekilde işlem yapılmasını önermemektedir. Oracle varchar2 data type’ ini sizin özel bir işlem yapmanıza gerek kalmadan number’ a convert etmektedir. Burada yapılan yanlışlıkla ilgili bir örnek verelim ;

Emp adında bir tablomuz var ve bu tablodaki employee_id alanının data type’ ı ise varchar2, bu tabloya aşağıdaki şekilde sorgu gönderdiğimizde;

select * from emp
where employee_id = 200

Plan
SELECT STATEMENT ALL_ROWS Cost: 3 Bytes: 125 Cardinality: 1
1 TABLE ACCESS FULL TABLE KAMIL.EMP Cost: 3 Bytes: 125 Cardinality: 1

full gitmektedir. Yukarıda oracle varchar2’ yi number’ a size yansıtmadan otomatik olarak convert ettiğini belirtmiştik peki bunu nasıl yapıyor ? Bunu yaparken sizin sorgunuzda siz farkında olmadan aşağıdaki şekilde çalıştırıyor.

select * from emp
where to_number(employee_id) = 200

Sorguyu olması gerektiği gibi düzenlediğimizde ise ;

Plan
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 125 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE KAMIL.EMP Cost: 1 Bytes: 125 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) KAMIL.INDX_EMP_ID_1 Cost: 0 Cardinality: 1

Indexi düzgün bir şekilde kullandığını görüyoruz.

Histogram Kullanımı

Histogramlar data dağılımını ifade eder ve tablo veya indexlerin istatistikleri toplanırken bu bilgiyi saklarlar. İndexler üzerinde histogram kullanımı sınırsızdır. Bir tablonun herhangi bir kolonu üzerinde histogram toplayabilirsiniz. Histogram toplayarak optimizer planın doğru çalışmasına yardımcı olmuş olursunuz. Çünkü histogram toplayarak sorgunun doğru index üzerinden çalışmasını sağlayabilirsiniz. Histogram toplarken spesifik size ‘ larda toplayabilirsiniz.

Fast Full Scan ;

Fast full scan yapılması demek b-tree index üzerindeki tüm blockların oracle tarafından okunması demektir. İndex birden bütün blokları aynı anda sırasıyla okur. Parallelde aynı anda kaç tane block okuyabileceği database deki DB_FILE_MULTIBLOCK_READ_COUNT parametresiyle set edilir. Fast full scan index, full table scan’ e daha az i/o yapmakta ve query’ nin daha hızlı çalışmasını sağlayacaktır.

İndexsin boyutu tablonun boyutu ile karşılaştırıldığında nipeten daha küçük olursa, fast full scan uygulama için ciddi bir performans sağlayabilir. Tablonun birden fazla kolonu kullanılarak oluşturulmuş olan concatenate indexlerin size’ ları tablonun gerçek size’ ından daha büyük olabilirler bu durumda fast full index’ in performansıda düşecektir.

SQL> create index sscan on emp (employee_id, job_id)
Index created.

select count(*) from emp
where job_id = ‘SH_CLERK’

Plan
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 9 Cardinality: 1
2 SORT AGGREGATE Bytes: 9 Cardinality: 1
1 INDEX FULL SCAN INDEX KAMIL.SSCAN Cost: 1 Bytes: 54 Cardinality: 6

Skip Scan ;

Skip-scan index, full scan index den daha hızlıdır. Okuma yaparken daha az performans kullanılmasını sağlarlar.

SQL>select count(*) from kamil.emp
2 where job_id = ‘SH_CLERK’;

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FULL SCAN| SSCAN | 6 | 54 | 1 (0)| 00:00:05 |
————————————————————————–

Statistics
———————————————————-
47 consistent gets
41 physical reads

SQL>select /*+ index_ss (a, sscan1) */ count(*) from kamil.emp1 a
2 where job_id = ‘SH_CLERK’ ;

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 9 | 107 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX SKIP SCAN| SSCAN1 | 6 | 54 | 107 (0)| 00:00:01 |
—————————————————————————

Statistics
———————————————————-
6 consistent gets
2 physical reads

İki sorgu arasındaki farklara bakacak olursanız phsical read miktarı full scan indexde 47 iken sckip scan index kullanıldığında 6 olarak gerçekleşmiştir.

Çok büyük tablolar üzerindeki oluşturulan composit indexlerde, ilk sırada belirleyici olarak kullanılması gereken kolon kullanılmasa bile yani ikinci sıradaki kolon kullanılsa bile performans açısından dataya hızlı erişim sağlayabilir.

İndex Unique Scan

Unique indexli kolon kullanilarak tablo select edilmeye çalisildiginda bu indexi kullanilir. Unique index scan genellikle esitlik, IN ve EXIST operatörleri kullanildiginda kullanilan bir index tipidir.

İndex Range Scan

Genel olarak <,>,<=,>= ve between operatörleri kullanildiginda kullanilan index tipidir.

Reserve-Order Index Range Scan

Script içerisinde desc yapıldığında kullanilan bir index tipidir.

İndex Full Scan

İndexli kolon üzerinden full okuma yapan index tipidir.

İndex Çeşitleri

İndex çeşitlerini aşağıdaki gibi detaylandırabiliriz ;

• B-tree İndexler
• Bitmap İndexler
• Hash İndexler
• İndex-organized table
• Reserve key İndexler
• Function-based İndexler
• Partitioned (local and global) İndexler
• Bitmap join indexler

Kısa kısa bu index çeşitlerinin üzerinden geçmeye çalışalım ;

B-Tree İndexler ;

Oracle’ ın default index type’ dır. Genel amaçlı olarak kullanılan indexlerdir. Create index komutu ile oluşturulan tüm indexler b-tree index dir. Tek bir kolon üzerine yada composit olarak (birden fazla kolon üzerinde) oluşturulabilirler. Üst limiti 32 kolon ile sınırlıdır. (Bu limite henüz ulaşanını görmediğimi özellikle belirtmek isterim )

Oracle indexli kolonlar içerisinde null değerler var ise bunları indexlememektedir. Dolayısıyla indexli kolonlar mutlaka dolu olmalıdır. Null kayıtlar içeren kolonlar üzerine index oluşturmayınız. B-tree indexlerin en temel özelliği seçiciliği son derece fazla olan kolonlar üzerinde oluştulmaktadır. OLTP sistemlerde yaygın olarak kullanılmasının nedenide budur aslında, indexler oluşturulurken hangi kolonlar üzerinde index oluşturulacağı dikkatli analiz edilmelidir.

Örneğin tablolar üzerindeki unique değer alan kolonlar, zaman ifade eden kolonlar b-tree index oluşturulması için ideal olan alanlardır.

Bitmap İndexler;

Bitmap indexler, b-tree indexlerin aksine daha çok datawarehouse sistemler ile karar destek sistemlerinde kullanılan bir index bir tipidir. OLTP sistemlerde kullanılmamalıdırlar. Bitmap indexler çok büyük tablolar üzerinde, önem seviyesi orta derecedeki sutunlarda kullanılarak dataya çok hızlı erişim sağlarlar. (orta derecedekinden kastedilen, number of distinct values değeridir, distinct edildiğinde sadece birkaç tane kayıt dönmelidir) Bitmap indexlerdeki kolon sınırıda 30’ dur. En fazla 30 kolon üzerinde bitmap index oluşturabilirsiniz ama genellikle az sayıda kolon kullanılarak oluşturulmaktadırlar.

Bitmap indexlere örnek verecek olursak, çok büyük bir tablonuz olduğunu düşünün buradada cinsiyet kolonu olsun. Bu kolonun alacağı değer Erkek ve Kadın olarak sadece 2 farklı değer alacaktır, database de çalıştırılan sorgularda da bu alanın sıkça kullanıldığını biliyor isek bizim için cinsiyet kolonu bitmap index oluşturulabilir bir alan olacaktır. Birden fazla composite bitmap indexler kullanıldığında ise, oracle çok çabuk istenmeyen datayı elimine etmek için, her bir indexli kolonu birleştirebilme yeteğine sahiptir.

Örnek create scripti ;

Create bitmap index ind1 on emp (cinsiyet) ;

şeklinde create edilebilmektedir.

Bitmap indexlerin kullanımı ile ilgili olarak sıkça kullanılan bir diğer örnek ise, katılımcı anketleridir. Buradaki evlilik durumu, eğitim durumu, gelir düzeyi gibi alanlar bitmap indexler için ideal alanlardır.

Bitmap İndexlerin Kısıtları ;

• Bitmap indexler, rule-based optimizer tarafından dikkate alınmazlar,
• Alter table komutu ve bitmap index bulunan kolonun modify edilmesi sonrasında, index invalid olmaktadır,
• Bitmap indexler sutundaki hiçbir veriyi içermezler ve hiçbir bütünlük kontrolünde kullanılamazlar,
• Bitmap indexler unique’ liği bildiremezler,
• Maximum 30 kolon üzerinde oluşturulabilirler.

Yoğun OLTP sistemlerde kesinlikle kullanılmaması gerekmektedir.

Hash İndexler ;

Hash indexler Hash cluster ile birlikte kullanılırlar. Cluster veya hash cluster create ettiğinizde, cluster key tanımlarsınız. Bu cluster key, tabloları cluster içerisinde nasıl store ettiğini anlatır. Data saklanmaya başladığı zaman, clusterla ilgili bütün rowlar aynı veritabanı blocklarında saklanır. Where cümleciğinde indexli kolon tam olarak eşleşerek hash index kullanıldığında, oracle hash fonksiyonunu kullanarak verilerek erişim sağlar. Burada data fiziksel olarak hash fonksiyonuna saklandığından dolayı, oracle çok çabukça dataya erişim sağlar.

Hash indexler potansiyel olarak databasedeki dataya erişmenin en hızlı yoludur fakat bunun bir takım sakıncalarıda vardır. Cluster keyin distinct edilmiş değeri (number of distinct value değeri) hash cluster oluşturulmadan önce bilinmesi gerekir . Bu değer oluşturulduğu zamanki değer belirtilen değerdir. Number of distinct değeri küçük kalırsa bunun maliyeti çok fazla olacaktır. Buda daha fazla sistem üzerinde daha fazla I/O yapılmasına neden olacaktır. Bu durum ek rowların saklanması için daha fazla buffer kullanılmasına, sonrasında da daha dazla I/O olmasına neden olacaktır. Hash value değerinin tablo içerisindeki number of distict değeri estimated edilenin altında kalması durumunda, cluster’ın drop – create edilerek değerin alter edilmesi gerekecektir. Burada Alter Cluster komutunun kullanımı Hashkeys değerini değiştirmeyecektir.
Hash clusterlar genellikle fazladan alan kullanırlar. Bu alanda belirli bir cluster key için, tüm satırları tutmak için ne kadar alan gerektiğini belirlemek mümkün değilse, alan boşuna kullanılıyor olabilir. Gelecekteki büyüme için cluster içerisinde ek alan tahsis etmek mümkün değilse, o zaman hash cluster iyi bir seçenek olmayabilir. Eğer uygulamanız cluster tablelar üzerinde sıkça full table scan yapıyorsa, yine hash cluster uygun bir seçenek olmayacaktır. Cluster içerisindeki free alan miktarı gelecekteki büyümeye izin vereceğinden, full table scan işlemleri çok daha fazla kaynak kullanımına yol açabilir.
Genellikle, hash index kullanımı öncelikle statik verilerde ve sıralı değerlerde kullanılmaktadır.
Hash index kullanımı where koşulunda tam bir değer belirtilen durumlardan ziyade bir değer aralığı belirtilen durumlarda kullanımı çok yararlıdır.
İndex –Organized Tables ;

İndex organized tablo, tablonun üzerindeki primary key tarafından, b-tree index sine göre tablonun storage yapısının değiştirilmesi üzerine kurulur. Tablonun bu unique’lik özelliği, diğer tablo yapılarında olduğu gibi DML ve DDL işlemlerine izin verir. Çünkü rowid bilgisi, tablonun yapısı ve tablo içerisindeki data ile bağlantılı değildir.
İndex-organized tablolar primary key sutunları üzerindeki tam eşleşme durumunda tablo içerisindeki veriye çok daha hızlı erişim sağlar. (key-based tabanlı erişim) Primary key alanı üzerindeki update ve insert komutları, satırlar fiziksel olarak sıralanacağından dolayı daha iyi bir performans gösterecektir.
Eğer index-organized tablo içerisinde, primary key kolonunu kullanan çok fazla query’ niz yoksa, ikincil index olarak diğer kolonlar üzerinde bu indexi oluşturabilirsiniz.
İndex organized tabloların, heap organized tablolara göre (default table yapısına göre) daha performanlı olduğunu söyleyebiliriz bunun nedeni, buradaki indexin range scan yöntemi ile kullanılmasından kaynaklıdır. Bu şekilde dataya daha hızlı erişim sağlanabilmektedir.
Reserve Key İndexes ;

Database’ e yüklü bir data load edilmeye çalışıldığında, index ile ilgili yoğun bir I/O oluşacağından bir bottleneck durumu ile karşılaşabilirsiniz. Veri atılmaya (load edilmeye) çalışılırken, indexin ve diskin bir kısmı, bir diğerinden çok daha büyük ölçüde kullanılabilir. Bu sorunu hafifletmek için, index tablespace’ i fiziksel olarak farklı bir disk grubunda tutulabilir.

Oracle bu tarz performans problemlerinin çözümü için farklı olarak reserve key indexleri sunar. Data reserve key indexlerde saklanırsa, bu datalar öncesinde indexler üzerinde store edilebilirler. Böylece 1234, 1235 ve 1236 değerleri 4321, 5321 ve 6321 olarak saklanır. Sonuç olarak, index insert edilen her bir kayıt için farklı index blocklarını update edebilir.

Eğer sınırlı sayıda diskiniz varsa ve eşzamanlı olarak büyük datalar geliyorsa, reserve key indexler uygulanabilir ve kalıcı bir çözüm olabilir.

Reserve key indexleri , bitmap veya index-organized indexlerle beraber kullanmamalısınız.

Function-Based İndexes ;

Tablolarınız üzerinde create edebileceğiniz bir diğer index çeşidimiz function-based indexlerdir. Function-based indexler olmadığında, bir sutun üzerinde function kullanılan hiçbir sorgu index kullanmayacaktır. Örneğin aşağıdaki sorgu function-based olarak index create edilmediği sürece index kullanmayacaktır ;

select * from kamil.emp
where upper(first_name) = ‘RONALDO’

Sorgu aşağıdaki gibi olduğunda first name kolonu üzerindeki indexi kullanacaktır ancak bu seferde sorgu no rows dönecektir çünkü içeride first name’ i Ronaldo veya ronaldo olacak şekilde kayıtlar olacaktır.

select * from kamil.emp
where first_name = ‘RONALDO’

First_name kolonu üzerine aşağıdaki gibi bir function-based index create ettiğimizde sorgu hem index kullanmış olacak hemde tüm case sensitive olmasına bakmadan (çünkü hepsini uppere çevirip match etmeye çalışacaktır) aradığımız tüm isimleri getirecektir.

create index indx_emp1_func_based on emp1 (UPPER(first_name));

Function-based indexler çok faydalıdırlar fakat fazla sayıda kullanılmamalıdırlar, yoksa tüm DML işlemlerinde ciddi performans kayıpları yaşayabilirsiniz.

Partition İndexes ;

Partition indexler basitçe, indexleri birden fazla parçaya bölmek olarak tanımlayabiliriz. İndexleri fiziksel olarak farklı parçalara bölmek , daha küçük parçalarve üzerinden daha hızlı erişim ve index parçalarını farklı disklere tanımladığımızda da I/O da da ciddi kazançlar sağlayacaktır. B-tree ve bitmap indexler partition yapıalbilirler. Hash index partition yapılamaz. Partitining yapmanın biçok farklı yolu vardır. Tablo parititionlı olabilir ancak indexler olmayabilir. Tablo partition’lı olmayabilir fakat indexler partition’lı yapılabilir. Her iki şekildede cost-based optimizer bunları kullanacaktır. Partitioning performansı artırmak ve bakımı kolaylaştırmak için birçok olanaklar sunar.

Partition indexlerin iki çeşidi vardır, Local ve global partition indexler olarak, her ikiside prefix ve nonprefix olarak iki altgrubu daha var diye söyleyebiliriz.

Local Partition İndexler; Sıkça kullanılan partition index çeşididir. Herbir partition’ ın local indexi sadece o table partition’ a ait rowid leri ve keyleri içerir. Local indexler b-tree veya bitmap olarak create edilebilirler. Eğer b-tree index yapılırsa, unique veya non-unique olarak oluşturulabilirler.

Local indeksler partition’ ının bağımsızlığını desteklemektedirler. Bu şu demek aslında; indexleri drop veya rebuild işlemi yapmadan, yeni partition ekleyebilir, truncate – drop edebilir, offline’ alabiliriz. Oracle local indexleri otomatik olarak yönetebilmektedir.

Global Partition İndexler ; Global partition indexler, birden fazla tablo partitionlarına ait keyleri tek bir index partition’ ında içerirler. Global index olarak sadece b-tree index create edilebilir. Bu indexler oracle tarafından otomatik olarak da manage edilmezler.

Bitmap Join İndexes ;

Bitmap join index, bitmap index tabanlı olup iki tane tablonun join edilmesiyle oluşturulurlar. Bitmap join indexler datawarehouse sistemlerde querylerin performansı yükseltmek amacıyla tabloları birleştirir. Eğer user query’ sinde bu iki tabloyu kullanıyor ise sorguda join kullanmasına gerek yok çünkü olması gereken join bitmap join üzerinden zaten kurulmuştur. BU şekilde daha fazla performance kazancı ve I/O da da ciddi düşüşler olacaktır.

Optimizer’ ın davranışı ile devam etmeyi planlıyorum.