Tablo içerisinde rastgele dağıtılmış kayıtları(rows) aranılan küçük bir set getirmek için kullanılırlar. Bilindiği gibi indeksler indeks segmentinde saklanır. Logical olarak hiyerarşik bir zincir oluşturup ve bu zincirin leaf(yaprak) seviyelerinde referans alınan tablo rowidleri ile aranılan datayı/dataları nokta atışı olarak bulmamızı sağlayan objelerdir.Disk I/O ‘unu azaltırlar.Eğer indeks kullanılmayan bir HOT(heap Organization Table) istenilen bir küme setini FTS(Full Table Scan) olarak tarayacaktır FTS demek, tablo içerisindeki tüm kayıtlardaki her satırı inceleyerek sorgulanan yöntemdir. Indeks kullanıldığında ise , aranılan veri/verilerin hızlı bir şekilde bulunup sorgu nun en kısa zamanda çalışması hedeflenir. Yanlış index kullanımı, yanlış indeks tipi seçimi, yanlış yazılamış Hintler, ve invalidate indeksler performansa kötü etki yapmaktadır.
İndeks oluşturma:
CREATE INDEX ord_customer_ix ON orders (customer_id);
İndex ne zaman kullanılmalıdır?
İndeks gerekliği, tablodaki tüm satırlara bakıldığında aranılan sorgu sonucunun tüm kayıtlara oranı olarak hesaplanır. Genel olarak %2 ile %10 arası indeks kullanılması tavsiye edilebilir.
Unique/Nonunique indexes:
Unique bir indeks , her bir tek değer için tek bir rowid içerir. Aynı olan kayıttan bir tane saklar. Leaf (yaprak)’ larda sıralı bir şekilde bu bilgiler tutulur.
NonUnique indeksler ise, birden fazla aynı datayı(örneğin isim column’ u için birden fazla Ahmet değeri gibi) tutmayı izin verir.Nonunique indeksler index leaf’lerda indeks key tarafından artan şekilde sıralanmış rowid’ ler içermektedir.
Not:Primary key ve unique constraintleri otoamitik olarak unique(benzersiz) index oluşturular.
İndekslerin karakteristik Özellikleri:
Unusable: Mevcut olan bir indeksi kullanılamaz duruma düşürmektir. Kullanılmak istenildiğinde tekrar rebuild edilerek usable duruma getirilebilir. Genellikle mevcut bir indeksi kullanmayı önlemek için unusable kullanılır. Unusable indeksler, DML(insert-update-delete-merge) operasyonlarında ve optimizer tarafından dikkate alınmazlar.Bir indeks unusable olduğu zaman data tarafından indeks segmentinden silinir. Default olarak oluşan tüm indeksler usable’dir.
Bir indexin usable/unusable olduğunu görebilmek için aşağıdaki sorguya bakmamız gerekir.
SELECT index_name, status
FROM user_indexes;
FROM user_indexes;
ALTER INDEX test_idx UNUSABLE;
Online yapmak için
ALTER INDEX test_idx_state REBUILD;
Invisible: Indeksi gizli duruma getirmektir. Bir invisible index DML operayonları tarafından dikkate alınırlar. Fakat optimizer tarafından dikkate alınmazlar. İndeksi invisible yapmadaki amaç, silinmiş gibi gösterip optimizer tarafından dikkate alınmayıp (indeks kullanmayıp)mevcut sorgulardaki performans testi yapmaktır. Default olarak tüm indeksler oluşturulduklarında visible’ dır.
Yeniden adlandırma:
ALTER INDEX test_idx RENAME TO i my_test_idx;
Rebuild etme:
ALTER INDEX my_test_idx REBUILD ONLINE;
Paralel özelliği verme:
ALTER INDEX my_test_idx PARALLEL 3;
Reverse özelliği verme:
ALTER INDEX _test_idx _person_id REBUILD REVERSE;
İstatistik toplama:
ALTER INDEX idx_last_name
REBUILD COMPUTE STATISTICS;
ifadesi indeks oluşturma esnasında istatistiklerin toplanması istenmeyebilir yada unutulabilir. Alter index komutuyla sonradan verilen indeks için istatistikler toplanabilir.
Index silme:
DROP INDEX idx_last_name;
Komutuyla verilen isimdeki bir indeksi silebiliriz.
B tree Index:
B tree indeks , Balanced Trees ifadesinin kısaltılmış halidir.
Örnek olarak employees tablosundaki department_id column için b tree indeks yapısına bakalım. Leaf seviyesinde önce value sonra rowid gelecek şekilde temsil edilir. Şuan burada kullanılan indeks nonunique bir indekstir. Zincirin leaf seviyesinde sıralı şekilde duplicate kayıtlar mevcuttur.
B tree indeksler veriyi sıralı bir şekilde kaydederler. dolayısıyla herhangi bir sort işlemini önlemek içinde kullanılırlar. Bitmap indexler için böyle bir durum söz konusu değildir.
Btree indeksler OLTP sistemler için idealdir.
B tree indeksler null değerler için index kullanamazlar.
Kullanımı:
CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, column_n) [ COMPUTE STATISTICS ];
Compute Statistics:
Genel olarak bir indeksi yukarıdaki şekilde oluştururuz.
COMPUTE STATISTICS, index oluşturma esnasında istatistiklerin oracle tarafından söylenilmesini bildirir.
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 :
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.
ÖRNEK:
SQL>create table xcount(t1 number,t2 number);
SQL> begin
2 for k in 0..10000
3 loop
4 insert into xcount values(k,mod(k,5));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>create index xc_idx1 on xcount(t1);
SQL>create index xc_idx1 on xcount(t2);
SQL>create index xc_idx3 on xcount(t1,t2);
SQL> set autotrace on
SQL> select * from xcount where t1=0 and t2=0 order by t1 desc;
T1 T2
---------- ----------
0 0
Execution Plan
----------------------------------------------------------
Plan hash value: 2128003261
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| XC_IDX3 | 1 | 26 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"=0 AND "T2"=0)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Yukarıdaki sorgumuz xc_idx3 indeksini kullanarak CPU bazında en düşük Cost'u çıkarıp en iyi execution planı seçmiştir. Şayet aynı sorguda xc_idx3 değilde xc_idx1 yada xc_idx 2 yi seçseydik o zaman sonuç aşağıdaki gibi olacaktı.
SQL> select /*+INDEX(xcount xc_idx2)*/ * from xcount where t1=0 and t2=0 order by t1 asc;
T1 T2
---------- ----------
0 0
Execution Plan
----------------------------------------------------------
Plan hash value: 3776712309
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 26 | 24 (0)| 00:
00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| XCOUNT | 1 | 26 | 24 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | XC_IDX2 | 2001 | | 6 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"=0)
2 - access("T2"=0)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Hint kullanarak xc_idx2 indeksini kullanarak sorguyu çalıştır dedik. Fakat böyle bir (t1 and t2) filtresinde CPU bazındaki cost'u 24 çıkmıştır.Combine indexler böyle durumlarda verimli olabilir.
Function-based Index:
indeksler sadece kolonlar için değil aynı zamanda fonksiyonlar içinde kullanılırlar.
ÖRNEK:
SQL>create index idx_adv_f_name on adv_table(lower(first_name));
index created.
SQL>create table adv_table as select * from hr.employees;
table created.
SQL> set timing on
SQL> set autotrace on
sQL> select first_name from adv_table where lower(first_name)='anthony';
FIRST_NAME
------------------------------
Anthony
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3152802285
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ADV_TABLE | 1 | 24 | 2 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ADV_F_NAME | 1 | | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("FIRST_NAME")='anthony')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
434 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
