Sql Server Full-Text Search

Merhaba Arkadaşlar,

Bugün full-text search (tüm metin araması) mimarisini sizlere anlatacağım.
İlk olarak full-text search mimarisi neden ortaya çıktığından bahsedeyim. Sql server’da arama işlemlerinde çoğunlukla like ifadesi(expression) kullanılır. Fakat like ifadesi aradığımız metin büyüdükçe performansında düşme göstermektedir. Küçük çaplı veritabanlarında like ifadesi ile istediklerimizi tabikide yapabiliriz ama veritabanındaki veriler on binlerin üstündeyse full-text search mimarisi bize gözle görülür bir fark yaratıcaktır.

Karakter içerikli kolonlarda nvarchar varchar gibi tiplerde max değerini verdiğimizde2gb’ta kadar veri arşivlenebiliyor. Bu yüzden bu tarz kolonlarımızda hızlı arama yapabilmek için full-text search mimariside bize kolaylık sağlamıştır. Full-text search sql server 2008 oncesinde bağımsız olarak çalışmaktaydı sonrasında ise veritabanıyla birlikte tutulmakta ve ‘sql full-text filter deamon launcher’ olarak hizmet vermektedir.

Hizmet bölümünden rahatlıkla görebilirsiniz bu özelliği (not: sql server kurulumunda 2008′den itibaren özellikler bölümünden full-text hizmetini aktif hale getirip kuruluma devam edilmelidir ya da varolan sql server’da tekrar kurulumu başlatıp özellik ekleme seçeneğinden (feature selection) full-text hizmeti aktif hale getirilmelidir.) ilerki anlatımlarımda kendim sql server 2012 üzerinden sizlere göstereceğim.

Full-text search servisinide aktif hale getirdikten sonra bu hizmetten yararlanmak için full-text index oluşturabiliriz. Onemli bir noktaya daha belirtmek isterimki bir tabloda sadece bir tane full-text index oluşturulabiliriz ve o tablodaki kolon ya da kolonları yapacağımız aramada rahatlıkla kullanabiliriz.Full-text search mimarisinin kullanıldığı veri tipleri sqlde şunlardır:char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary(max) ve FILESTREAM.

Full-text uygulayacağımız tabloda en az 1 benzersiz anahtar olması gerekmekte (unique key).

Full-text search mimarisi 3 adımda oluşturabiliriz.

Full-text kataloğu
Full-text index oluşturma
Full-text index iceriğinin oluşması

Full-Text Kataloğu Oluşturmak

Oluşturacağım katalog ve diger islemleri Northwind veritabanı içinde gerçekleştireceğim.
Artık başlayalım oluşturmaya sql server management studio’muzu acıyoruz ve oluşturulacak veritabanımızın alt klasörlerinden Storage klasörünü açıp Full-Text Catalog klasörüne sağ tıklıyoruz.

Resimlerle daha anlaşılır bir şekilde devam edeceğim.
Full-text catalog name yerine uygun isim verdikten sonra seçenekler (Options) bölümünde Set as Default catalog sekmesini aktif hale getirirsek eğer bundan sonraki Full-text indeksler için kullanılacağını gösteriyoruz.
Son seçeneğimiz ise Accent sensitivity özelliğidir bu özellikle farklı dillerde yapılan kayıtların aksan farklılıklarını ayarlar.
Örneğin: Eğer Sensitive özelliğini seçersek Canapé ile Canape kelimeleri aynı olmayacaktır fakat insensitive seçilirse 2 kelimede aynı olarak değerlendirilip ona göre sonuç dönecektir.

Bu ayarlamalarıda yaptıktan sonra tamam tuşuna basıp kataloğumuzu oluşturmuş oluyoruz.

Bu işlemleri t-sql kullanarakta gerçekleştirebiliriz.

create FULLTEXT Catalog
Northwind_FTS
with Accent_sensitivity = off
as Default

Full-Text Indeks Oluşturmak

Gelelim indeksimizi oluşturmaya northwind veritabanındaki kategoriler tablosuna full-text index yaratalım. Resimde de gözüktüğü gibi ilk olarak full-text index sekmesinden define full-text index seçeneğine tıklıyoruz.

Karşımıza Full-text indexing wizard penceresi gelicek next’e basıp bir sonraki ekrana geçiyoruz.

Tablomuzda en az 1 tane eşsiz anahtar olmalı demiştik. Bunun nedeni full-text katalogunda tutulan veriler indekslendikten sonra bu anahtar sayesinde geriye kalan verilere erişilecektir.

Select table columns ekranında otomatik olarak tablomuzda bunulan kolonlar listelenecektir. Tekrar hatırlatmakta fayda var listelenen kolonlar char, varchar, nchar, nvarchar, text, ntext, image gibi veri tipleri ise gözükmektedir.

Belirleyeceğimiz kolonun sırasında dil seçeneği mevcuttur burada seçilen dil hangisi ise ona göre indexler oluşacak. Yaklasık 40′dan fazla dil seçeneğini destekler ve sizin tablonuzdaki veriler hangi dilde ise onu tercih etmenizde tabi ki yarar vardır.

Bir diğer özellik ise ‘type column’ sadece varbinary(max) ve image veri tiplerinde geçerlidir. Eğer seçeceğiniz kolon resim tipindeyse type column özelliği boş geçilmemesi gerekmektedir. Bu 2 veri tipinde arama yapılamayacagı için image ve varbinary tipindekilerini özetleyecek başka veriye ihtiyaç duyulur. Bu yüzden Type Column bize bu işte kolaylık sağlamaktadır. Ayarlarımızı yaptıktan sonra ilerliyoruz.

Indexleme biçiminde otomatik olarak yapılmasında fayda vardır. Bu seçenek, tabloda yapılan ekleme, silme ya da güncelleme işlemlerinde otomatik olarak tablo index’ne yansıtıcaktır.

Full text indeksimizi oluştururken en başta yaptığımız katalogumuz gözükmektedir. Dilerseniz başka bir katalog ekleyip onu kullanabilirsiniz.File group ilk değeri (Default) primary’dir. Bir diğer özellikte Stoplist özelliğidir. Stoplist isminden de anlaşılacağı gibi bazı kelime ya da kelimeleri arama sırasında dikkate almayacagını belirtir. Stoplist olmadığı için system diyerek ya da kapatarak ayarlarımızı yapıyoruz.
Veritabanınıza Stoplist kurmak için http://msdn.microsoft.com/en-us/library/ms142551.aspxsize yardımcı olucaktır.

Bir diğer ekranımız ise isteğe bağlı olarak yapılacak olan full-text indeksimizin belli bir zamanda oluşup oluşmamasını ayarlamaktır.
İki seçeneğin ilki mevcut tablo uzerinde full-text index otomatik olarak oluşturulurken , ikincisinde full-text katalog yeniden oluşturulmaktadır.

Son adımımızda onceki etaplarda neler yaptığımızın kısa bir özeti gösterilmektedir ve son adım olarak finish butonuna tıklayıp full-indeximizi oluşturmaktayız.

Full-Text Indeks İçeriğini Oluşturmak

Normalde bu işlemin otomatik olarak yapılması gerekmektedir. İçeriğin oluşmasını sağlayacak 2 adım vardır bunlarıda bilmeliyiz. Mevcut full-text indeksimizin yapıldığı tablomuzun üstüne sağ tıklayıp resimdeki ilerlemeyi izliyoruz ve karşımıza ‘Start Full Population’ ile ‘Start Incremental Population’ sekmeleri geliyor.
SFP : indexi en baştan oluşturmak için kullanılır.
SIP : mevcut index başlatılmış ve son halinden devam edilerek indeksin doldurulacağı zaman kullanılır.
İlk kez doldurulacağı için start full population yaparak bu etabıda başarıyla bitiriyoruz.

Artık full-text indeximizide oluşturup doldurduğumuza göre sıra arama işlemlerine geldi

Full-Text İçinde Arama Yapmak

Sql server full-text için kullanıcağımız bazı fonksiyonları sizlere anlatacağım.
Bunlar:

Contains
ContainsTable
Freetext
FreetextTable

Contains Fonksiyounu

--Contains fonksiyonu 2 parametre ile çalışmaktadır
--aranan kelimenin aynısı olması gerekmektedir.
--ilki full-text indeks yaparken seçtiğimiz kolon olması gerekmektedir. 
--Full-text index olarak tanıtılmayan kolon hata verecektir.
--ikincisi aranacak kelime ya da kelimeler
select * from Kategoriler where contains(KategoriAdi,'Seafood')
--Eger yanlıs kolon yazarsak hata verir.
--Msg 7601, Level 16, State 3, Line 1
--Cannot use a CONTAINS or FREETEXT predicate on column 'KategoriAdi' 
--because it is not full-text indexed.
select * from Kategoriler where contains(Aciklama,'and')
select * from Kategoriler where contains(Aciklama,'drinks')
select * from Kategoriler where contains(Aciklama,'drink')
select * from Kategoriler where contains(Aciklama,'sweet')

Freetext Fonksiyounu

--freetext fonksiyonu
--contains fonksiyonunu cok benzemektedir tek farkı
--contains fonksiyonunda aranan kelimenin aynısı geçmesi gerekirken
--freetext fonk. aranan kelime ve ona benzeyen kelimeler bulunmaktadır.
select * from Kategoriler where freetext(Aciklama,'and')
select * from Kategoriler where freetext(Aciklama,'drinks')
select * from Kategoriler where freetext(Aciklama,'drink')
--contains ile drink arandığında sonuc bulunmazken
--freetext ile drinks ifadeside sonuc olarak gelmektedir.
select * from Kategoriler where freetext(Aciklama,'sweet')

ContainsTable Fonksiyounu

--ContainsTable Fonksiyonu
--aranan kelimenin derecesinide gösterir.
--Arana kelime farklı verilerde geçebilir
--Aranan kelimenin en cok geçtiği kayıtları göstermek isteyebilirsiniz.
--ozaman containstable fonksiyonunu kullanmanız gerekecektir.
--containstable 3 parametre alır ve tablo döndürür
--ilki işlem yapılacak tablo ismi
--ikincisi tablodaki full-text index olan kolon ismi
--üçüncüsü aranacak kelime
--containstable geriye key ve rank kolonlarından oluşan tablo döndürür
--key ifadesi bulunan verinin id numarasıdır
--rank ifadesi aranan kelimenin derecesidir.
go
select * from Kategoriler kat
inner join  containstable(Kategoriler,aciklama,'drinks') as con
on kat.KategoriID = con.[key]
go
select * from Kategoriler kat
inner join  containstable(Kategoriler,aciklama,'sweet') as con
on kat.KategoriID = con.[key]
go
select * from Kategoriler kat
inner join  containstable(Kategoriler,aciklama,'and') as con
on kat.KategoriID = con.[key]
go
--rank'a görede where koşulu yapabiliriz ve 30'dan yüksek olanları
--sırala diyip dönen sonucları karşılastırabiliriz.
select * from Kategoriler kat
inner join  containstable(Kategoriler,aciklama,'and') as con
on kat.KategoriID = con.[key]
where con.[rank]>=30

FreetextTable Fonksiyounu

--freetexttabledaki rank hesaplanırken kelimenin aynısı ile birlikte ona benzeyen
--kelimelerinde geçmesinden dolayı rank yüksek çıkabilir.
--Drinks ya da drink yazsamda aranan kelimenin aynısını ya da benzerini getircek gene.
select * from Kategoriler kat
inner join  freetexttable(Kategoriler,aciklama,'drink or drinks') as con
on kat.KategoriID = con.[key]
go
select * from Kategoriler kat
inner join  freetexttable(Kategoriler,aciklama,'sweet and savory') as con
on kat.KategoriID = con.[key]
--görüldüğü gibi rank derecesi hem sweet ve hem savory'i bulunca artıyor.
--bunu where koşulu ile rank 0 olanları gösterme diyebiliriz ve sonucları görebiliriz.
go
select * from Kategoriler kat
inner join  freetexttable(Kategoriler,aciklama,'sweet and savory') as con
on kat.KategoriID = con.[key]
where con.[rank] not in (0)
go
select * from Kategoriler kat
inner join  freetexttable(Kategoriler,aciklama,'and') as con
on kat.KategoriID = con.[key]
go
--rank'a görede where koşulu yapabiliriz ve 300'den yüksek olanları
--sırala diyip dönen sonucları karşılastırabiliriz.
select * from Kategoriler kat
inner join  freetexttable(Kategoriler,aciklama,'and') as con
on kat.KategoriID = con.[key]
where con.[rank]>=300