Merhabalar,
<Resimleri büyütmek için tıklayınız ..>
Bugünkü yazımızda full-text-index yapısı hakkında bilgi vermeye çalışıyor olacağız.B-tree yapısı içerisinde genelde index olarak hali hazırda clustered yada non-clustered index kullanılır.Page’lerde satır bazlı tutulan clustered ve non-clustered indexlerin yanısıra sütun bazlı tutulan bir de cloumnstore index’ler mevcuttur.Columnstore index ler daha çok DWH (DataWareHouse) sistemlerde kullanılmaktadır (insert-update-delete işlemlerine izin vermediğinden).
Index’ler,sistemlerde sorgularınızın daha hızlı bir şekilde getirilmesi amaçlı kullanılmaktadır.Index’lerin kullanımı ile ilgili hazırladığım demo yu “Video” bölümünden izleyerek ek bilgi sahibi olabilirsiniz.
Index kullanmadığımız tablolarda query’leri çalıştırdığımızda sorgu sonucu table scan işlem yapacağından çok fazla performans harcayacaktır.Bu gibi durumlarda karakter bazlı verilere karşı sql server,full-text-search ü destekler bir yapı sunmaktadır.Adından da anlaşılacağı üzere “tam metinsel arama” yapmak üzere geliştirilmiştir.Full-text-search için,full-text index ilgili kolonlara tanımlanmak zorundadır.Tanımlandığı kolonların karakter veri tipinde olmaları gerekir.N’char,N’varchar,ntext yada binary veri tipinde olmaları gerekir (image,varbinary vs).
Full-text indeks’in tanımlandığı kolondaki veriler kelimelerden oluşuyorsa,yani indexlenmiş metin “Her şey vatan için” şeklinde bir veriyi tutuyorsa,full-text-index 4 farklı dizine alır bunu yani “her”,”şey”,”vatan”,”için” gibi.Böylece belirteçlere ayrılmış metinler üzerinde aramalar yapıldığında kolay bir şekilde bunları bulabilmektedir.”Control-F” ile bir dosya üzerinde arama yapmak gibi düşünebiliriz bu kısmı.Ayrıca harf duyarlılığı özelliğini kaldırdığınızda “klemlre” yazsanızda “kelimeler” yazsanızda aynı sonucu alırsınız.Full-text-search mimarisini oluşturmak için 4 adımlık bir yol izliyoruz.
1-Full-Text Catalog Oluşturma
Veritabanı için oluşturacağımız full-text catalog birden fazla full-text index e sahip olabilir ama her bir index bir catalog a bağlıdır.Veritabanı da birden fazla catalog a sahip olabilir.Şimdi öncelikle veritabanı ->storage->full text catalogs ‘tan new diyoruz.
Gelen ekranda “name” kısmına uygun bir isim verip “owner” kısmına bir veritabanı rolü ya da kullanıcı atıyoruz.”Set as default catalog” seçeneğini tiklersek full-text catalog belirtilmemişse,oluşturulmuş bir full-text index varsa bu default catalog kullanılır,alt kısımda da case-sensitive i “insensitive” yaparak harfe duyarsız hale getiriyoruz.Ok deyip catalog u oluşturmuş oluyoruz.
“SELECT * FROM sys.fulltext_catalogs” sorgusuyla veritabanı üzerinde oluşturduğumuz full-text catalog larıda görebiliriz.Bizim oluşturduğumuz FTC un ID’si 6’ymış.”ID”,catalog larla ilgili diğer ifadelerde de kullanmak için lazım olacak.
2-Full-Text-Index Oluşturma
Catalog u oluşturduktan sonra,index ini de oluşturabiliriz şimdi.Catalog u oluşturmasaydık,index tanımladığımızda default olarak database in catalog una iliştirileceğini söylemiştik.Index’i table-level olarak tanımlayabiliyoruz ve her bir tablo için bir tane full-text index oluşturabiliyoruz.Tablonun full-text index i destekleyebilmesi için bir unique index tanımlanması zorunludur ve de index tek bir kolon a uygulanabilir o kolonunda boş bırakılamaz olması gerekmektedir.Bahsettiğimiz unique index i “key index” ile integer bir alana tanımlarız ya da primary key tanımlanmış kolonda işimizi görür.
Öncelikle full-text için dil kodunu seçmemiz gerekeceğinden aşağıdaki sorguyu çalıştırıp ülkelerin dil kodlarına bakıyoruz ;
Collation ayarlarımıza göre biz “1033” kodunu kullanacağız.Şimdi bir tablo seçip index oluşturuyoruz ;
Tabloya sağ tıklayıp,full-text index->define full-text index diyoruz.Gelen ekrana “next” diyoruz.
Bir sonraki ekranda Unique key olarak primary key olanı seçiyoruz.Çünkü zaten primary olarak seçilmiş alanda aynı kayıtların olması mümkün değildir…
“Select Table Columns” bölümünde sadece full-text search ün desteklediği veri tiplerine sahip kolonlar listelenir.Biz “name” kolonuna kuracağız.Dil olarakta AdventureWorks2014 ü kullandığımız için “british english” i seçiyoruz çünkü kullandığımız veritabanındaki kayıtlar ingilizce.TypeColumn ise varbinary ve image veri tipindeki kolonları için geçerlidir.Next diyerek devam ediyoruz.
Bir sonraki ekranda “select change tracking” bölümü yer almaktadır.Bu kısımda “automatically” seçeneğini seçersek indexin olduğu kolonda yapılacak değişiklikleri (insert-update-delete) otomatik olarak yansıtacaktır.Bunu seçip next diyoruz ;
Bu ekranda da,text catalog olarak daha önce oluşturduğumuz catalog u seçiyoruz.Hemen altında create a new catalog diyerek başka bir tane daha catalog oluşturabilirz.Index file group kısmında,eğer seçilen tablo bölünmüşse primary olarak işaretlenir,bölünmemişse “default” olarak bırakılır. “StopList” kısmında,arama yaparken dikkate alınmayacak kelime yada kelimeleri belirtmemizi sağlar.Sistemimizde de ;
kayıtlı bir liste olmadığı için bu seçenek için “off” diyebiliriz.Next diyoruz ;
Bu ekranımızda da opsiyonel olarak bırakılmış,”new table schedule” da belirli zamanlarda full-text index in yeniden oluşmasını sağlar,”new catalog schedule” da ise yine belirlediğimiz zamanlarda catalog yeniden oluşacaktır.
Örnek bir otomatik index oluşturma sayfasıda yine daha önce oluşturduğumuz jop schedule larına benzemektedir.Next deyip devam ediyoruz ;
Son olarak,yaptığımız değişiklikleri görüyoruz,bu kısmı da finish diyerek bitiriyoruz ve en sevdiğimiz ekran karşımıza geliyor :)) …..
Hangi full-text index hangi catalog a bağlı onu da şu query ile görebiliriz ;
SELECT t.name AS TableName, c.name AS FTCatalogName
FROM sys.tables t JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
Tablomuza sağ tıklayıp full text index->start full population diyerek bu adımımızıda tamamlıyoruz ;
3-FULL-TEXT-SEARCH
Full-text-search için kullanılan bir kaç fonksiyon vardır ki bunların en çok kullanılanlarını kullanarak işlem yapacağız ;
3-1) Contains
Bu fonksiyon 2 parametre alır.Aranacak olan kısma aranan kelimenin aynısı yazılır.Full-text olarak tanıtılmamış kolonlarda hata verir.
select * from Production.Product where contains(Name,’ball’)
Name kolonu altında “ball” kelimesi geçen tüm satırları çekti böylece.Full-text-index tanımlamadığımız kolonlarda da zaten o kolonda tanımlamadığınız ile ilgili bir uyarı alacaksınızdır.”balls” olarak arattığımda ise boş bir tablo ile karşılaşıyorum.
3-2)ContainsTable
select * from Production.Product pp
inner join containstable(Production.Product,Name,’ball’) as ppn
on pp.ProductID = ppn.[key]
Bu fonksiyon ile ek olarak sorgu sonucunda tabloya “key”,”rank” kolonları oluşmaktadır.Key -> id olarak verdiğimiz değerler olurken rank kolonunda da aranan kelimenin derecesini vermektedir.Farklı olarak,sorgu da kendi tablo içinde bir inner join atılma durumu söz konusu farkettiyseniz.
3-3) FreeTextTable
select * from Production.Product pp
inner join freetexttable(Production.Product,Name,’flat or ball’) as ppn
on pp.ProductID = ppn.[key]
Freetexttable fonksiyonu içinde ne aratırsanız içinde geçen kelimelere göre size sorgu sonucunu getirir.Döndürdüğü tabloya yine key ve rank kolonlarını ekler ve o şekilde sorgu sonucunu getirir.
Umarım faydalı olmuştur,iyi çalışmalar….
{ } Veritabanı & Yazılım Geliştirme 