Bu yazımda indexlerin kullanımı ile ilgili çok ufak ama son derece önemli olduğunu düşündüğüm birkaç örnek göstereceğim.
Index, bir kitabın sayfalarının tutulması, word dökümanında giriş sayfasından sonra gelen içindekiler bölümü veya süper marketlerdeki yiyecek bölümlerinin ayrılması gibidir. Bunların temel amacı aslında istenilen noktaya en hızlı şekilde ulaşmaktır. Peki neden en hızlı şekilde ulaşmak isteyelim? Cevap basit, "vakit, nakittir". Dolayısıyla veritabanının performansı için çoğu zaman index kullanmak isteyebiliriz. Tabii ki yeri geliyor ki index kullanmanın da full table scan yapmaktan daha berbar bir çözüm olduğunu görüyoruz.
Indexlerinde kendi içinde farklı farklı tipleri vardır. Bu index tiplerinin belirlenmesi veritabanındaki genel yapı ve tablonun içeriğine göre değişkenlik gösterebilir. B-tree indexlerin kullanılacağı bir tablodaki performans düşükken bu indexi reverse etmenin ya da bitmap index'e çevirmenin performans üzerindeki etkileri inanılmaz boyutlarda olabilir.
Ufak örneklerle başlayalım. (sys ile bağlıyız)
SQL> create user ogan identified by deneme;
User created.
SQL> grant connect,resource to ogan;
Grant succeeded.
SQL> conn ogan/deneme
Connected.
SQL> set linesize 600
SQL> set pagesize 150
SQL> create table ogan_deneme
2 (
3 username varchar2(40),
4 userid number (10)
5 );
Table created.
SQL> select * from user_indexes;
no rows selected
SQL> select * from user_constraints;
no rows selected
Bu aşamada birkaç veri girişi yapalım...
SQL> insert into ogan_deneme
2 values ('ogan',12345);
1 row created.
SQL> insert into ogan_deneme select * from ogan_deneme;
1 row created.
SQL> /
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> select count(*) from ogan_deneme;
COUNT(*)
--------------
256
SQL> select * from ogan_deneme where userid=12345;
256 rows selected.
Elapsed: 00:00:01.88
Execution Plan----------------------------------------------------------
Plan hash value: 1896958321
---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------
0 SELECT STATEMENT 256 8960 3 (0) 00:00:01
* 1 TABLE ACCESS FULL OGAN_DENEME 256 8960 3 (0) 00:00:01
--------------------------------------------------------------------------------
Statistics----------------------------------------------------------
5 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
3908 bytes sent via SQL*Net to client
587 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
Bu aşamadan sonra index yaratalım ve neler olduğunu görelim. Yaratacağımız index bir b-tree index olacaktır.
SQL> create index index_deneme on ogan_deneme(userid);
Index created.
SQL> select index_name, table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_DENEME OGAN_DENEME
SQL> select * from ogan_deneme where userid=12345;
256 rows selected.
Elapsed: 00:00:00.53
Execution Plan----------------------------------------------------------
Plan hash value: 4118286141
--------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------------
0 SELECT STATEMENT 256 8960 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 256 8960 2 (0) 00:00:01
* 2 INDEX RANGE SCAN INDEX_DENEME 256 1 (0) 00:00:01
--------------------------------------------------------------------------------------------
Statistics----------------------------------------------------------
13 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
3908 bytes sent via SQL*Net to client
587 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
256 rows processed
Daha önce çalıştırdığımız full table scan, içerik duplicate olduğu için 32 consistent gets çıktısı verdi. Ancak Index range scan ile arattığımız zaman bu rakam 46'ya ulaştı. Yani, veritabanının okuduğu alan sayısı arttı ve biz bunun düşük olmasını bekliyorduk. Dolayısıyla performans adına pek iç açıcı bir sonuç olmadığını görüyoruz.
SQL> drop index index_deneme;
Index dropped.
SQL> drop table ogan_deneme purge;
Table dropped.
SQL> create table ogan_deneme
2
(
3
username varchar2(40),
4 userid number(10)
5 );
Table created.
SQL> create index index_deneme on ogan_deneme(userid);
Index created.
SQL> insert into ogan_deneme 2 values('ogan',12345);
1 row created.
SQL> insert into ogan_deneme 2 values('ahmet',54321);
1 row created.
SQL> insert into ogan_deneme 2 values('mehmet',32145);
1 row created.
SQL> insert into ogan_deneme 2 values('ali',54123);
1 row created.
SQL> insert into ogan_deneme select * from ogan_deneme;
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> select * from ogan_deneme;
2048 rows selected.
Elapsed: 00:00:03.18
Execution Plan----------------------------------------------------------
Plan hash value: 1896958321
---------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------- 0 SELECT STATEMENT 2048 71680 3 (0) 00:00:01 1 TABLE ACCESS FULL OGAN_DENEME 2048 71680 3 (0) 00:00:01 ---------------------------------------------------------------------------------
Statistics----------------------------------------------------------
152 consistent gets
2048 rows processed
Şimdi bir de index range scan ile bakalım duruma...
SQL> select * from ogan_deneme where userid=12345 or userid=54321;
1024 rows selected.
Execution Plan----------------------------------------------------------
Plan hash value: 1212428791
--------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1024 35840 2 (0) 00:00:01 1 INLIST ITERATOR 2 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 1024 35840 2 (0) 00:00:01 * 3 INDEX RANGE SCAN INDEX_DENEME 8 2 (0) 00:00:01 ---------------------------------------------------------------------------------------------
Statistics----------------------------------------------------------
154 consistent gets
1024 rows processed
Consistent gets 2 arttı. Şu aşamada sonuç olarak yine amaçsız bir index olarak karşımızda duruyor olabilir. Şimdi bu indeximizi işe yarar hale getirebilmek için tabloda birkaç değişiklik gerçekleştirelim.
SQL> drop index index_deneme;
Index dropped.
SQL> truncate table ogan_deneme;
Table truncated.
SQL> insert into ogan_deneme
2 values('ogan',12345);
1 row created.
SQL> insert into ogan_deneme
2 values ('ahmet',99999);
1 row created.
SQL> insert into ogan_deneme
2 values ('mehmet',88881);
1 row created.
SQL> insert into ogan_deneme
2 values ('ali',12333);
1 row created.
SQL> insert into ogan_deneme
2 values ('veli',24144);
1 row created.
SQL> select * from ogan.ogan_deneme;
Execution Plan----------------------------------------------------------Plan hash value: 1896958321
--------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------- 0 SELECT STATEMENT 5 175 3 (0) 00:00:01 1 TABLE ACCESS FULL OGAN_DENEME 5 175 3 (0) 00:00:01 ---------------------------------------------------------------------------------
Statistics----------------------------------------------------------
16 consistent gets
5 rows processed
Şimdi index yaratalım...
SQL> create index index_deneme on ogan_deneme(userid);
Index created.
SQL> select * from ogan_deneme where userid=12345;
Execution Plan----------------------------------------------------------
Plan hash value: 4118286141
-------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 35 2 (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 1 35 2 (0) 00:00:01 * 2 INDEX RANGE SCAN INDEX_DENEME 1 1 (0) 00:00:01 --------------------------------------------------------------------------------------------
Statistics----------------------------------------------------------
3 consistent gets
1 rows processed
Çok az sayıda ama birbirinden farklı verileri barındıran bir tabloda index yaratılır mı? sorusunun en güzel cevabı. Tam 13 consistent gets daha düşük çıktı aldık. Sadece bir index yaratarak. Dolayısıyla 3-5 verili bir tabloda da index yaratmanın önemi çok açık olarak ortada. Bu tabloya, özellikle OLTP sistemlerde günde milyonlarca defa giriş olabileceğini düşünürseniz inanılmaz bir zaman kazancı olacaktır.
Indexler durduğu yerde disable / unusable olmazlar. Tablo'da bir movement olduğu zaman (alter table deneme move; , alter table deneme shrink space; gibi)
SQL> select * from ogan_deneme where userid=12345;
Execution Plan----------------------------------------------------------
Plan hash value: 1896958321
--------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 35 3 (0) 00:00:01 * 1 TABLE ACCESS FULL OGAN_DENEME 1 35 3 (0) 00:00:01 ---------------------------------------------------------------------------------
Tablo'yu move ettik ve blockların bulunduğu yerleri değiştirdik. Dolayısıyla index disable duruma düştü.
SQL> alter index index_deneme rebuild online;
Bu noktada unutmamak gereken durum şudur. Index rebuild edilirken, table'da exclusive lock meydana gelir ve kimse tabloya erişemez. Yani, büyük çapta bir indexin çok uzun sürebileceğini düşünürsek sıkıntı olabilir.
SQL> select * from ogan_deneme where userid=12345;
Execution Plan----------------------------------------------------------
Plan hash value: 4118286141
-------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 35 2 (0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID OGAN_DENEME 1 35 2 (0) 00:00:01 * 2 INDEX RANGE SCAN INDEX_DENEME 1 1 (0) 00:00:01 --------------------------------------------------------------------------------------------
Index'i rebuild ettiğimiz zaman tekrar kullanılır duruma kavuştu. Az önceki gibi düşük sayıda farklı veriler içeren bir tablo'da bu sıkıntı olsaydı rebuild sırasında da sıkıntı yaşamazdık. Yalnız rebuild ederken index'in size'ı son derece dikkatle izlenmelidir.
Index'ler üzerine konuşmakla bitiremeyiz. Yukarıdaki örnekler sadece b-tree indexlerin unique olanlarıdır. Bunun reverse key, bitmap, partitioned, non-partitioned vb tipleri de mevcuttur. Partitionedların içerisinde global local ve bunların dışında fake indexler gibi index tiplerini de eklersek herbiri ayrı ayrı bir konu olarak saatlerce anlatılabilir. Best Practicelerini de eklersek Oracle içinde apayrı bir uzmanlık alanı bile oluşturabilecek kadar geniş bir konudur.
Bir veritabanının performansının önemli ölçüde değişmesini (iyileşmesini) istiyorsak, index yaratmalıyız demek zor ancak sağlıklı indexler ve doğru tercihler ile son derece faydalı olacaktır.
İyi geceler,
Ogan
Hiç yorum yok:
Yorum Gönder