SQL SERVER FULL-TEXT SEARCH

Bu makalede Sql Server ’da metin tabanlı veriler üzerinde etkili arama performansı sağlayan Full-Text Search ’ten bahsedeceğim. Full-Text Search işlemlerini yapabilmesi için Full-Text index kolona sahip olmamız gerekmektedir. Full-Text indexler, metin içeriği barındıran ve belirli veri türlerine sahip kolonlarda oluşturulabilir. Bu veri türleri “Char, Varchar, NChar, NVarchar, Text, NText, XML, Varbinary, Image ” yer alır. Full-Text index oluşturulabilmesi için ilgili tabloda tek kolonlu bir unique bir index bulunması zorunlu bir koşuldur. Bu kısım biraz daha detaylandıralım, örnek olarak mevcut ortamda bulunan tablonuzda “Id” ve “Tarih” kolonları ile partition yapınız var bu yapıda mevcut partition index unique durumda bulunuyor ama yine de full-text index oluşturamıyorsunuz çünkü tek kolonlu bir unique index gerekmektedir. Benim test ettiğim yöntemlerden birisi de “Id” ve “Tarih” kolonları ile computed column oluşturarak bu kolona unique index oluşturmaya çalışmak oldu bu yöntem ile görece unique bir kolon oluşturarak bu kolona index oluşturulabilir ancak bu computed column oluştururken tablonuza […]

SQL SERVER FULL-TEXT SEARCH

Bu makalede Sql Server ’da metin tabanlı veriler üzerinde etkili arama performansı sağlayan Full-Text Search ’ten bahsedeceğim.

Full-Text Search işlemlerini yapabilmesi için Full-Text index kolona sahip olmamız gerekmektedir. Full-Text indexler, metin içeriği barındıran ve belirli veri türlerine sahip kolonlarda oluşturulabilir. Bu veri türleri “Char, Varchar, NChar, NVarchar, Text, NText, XML, Varbinary, Image ” yer alır.

Full-Text index oluşturulabilmesi için ilgili tabloda tek kolonlu bir unique bir index bulunması zorunlu bir koşuldur. Bu kısım biraz daha detaylandıralım, örnek olarak mevcut ortamda bulunan tablonuzda “Id” ve “Tarih” kolonları ile partition yapınız var bu yapıda mevcut partition index unique durumda bulunuyor ama yine de full-text index oluşturamıyorsunuz çünkü tek kolonlu bir unique index gerekmektedir. Benim test ettiğim yöntemlerden birisi de “Id” ve “Tarih” kolonları ile computed column oluşturarak bu kolona unique index oluşturmaya çalışmak oldu bu yöntem ile görece unique bir kolon oluşturarak bu kolona index oluşturulabilir ancak bu computed column oluştururken tablonuza oluşturacağı maliyeti ve transaction log file oluşacak büyümeyi de unutmamalısınız, bu konuda sizin de aklınıza gelecek farklı yöntemler olursa yorumlarda benimle paylaşırsanız sevinirim 🙂

Görsel – 1

Full-Text Search ’in Temel Bileşenleri

  • Full-Text Index: Metin veya büyük verilerde arama yapmayı hedeflediğimiz belirli metin sütun veya sütunlara uygulanır. Full-Text Index ile normal bir indeksler kullanım alanları ve işlevleri bakımından birbirinden oldukça farklıdır, bu kısımda biraz daha detaya girerek karşılaştırmasını yapabiliriz;
    • Normal indeksler, belirli sütunlarda bulunan veri erişimini hızlandırmak için kullanılır örneğin, TcKimlik kolonu veya Tarih kolonu gibiyken; Full-Text Indexler, büyük metin verilerin bulunduğu kolonlarda anahtar kelime veya kelime öbeği gibi metin tabanlı arama yapmak için kullanılır örneğin, açıklama kolonu içerisinde geçen bir kelimeyi aramak gibi.
    • Normal indeksler, B-Tree yapısını kullanarak sütunda arama yaparken; Full-Text Index, ilgili kolonda bulunan metin kelimelere ayrılarak dizinlenir.
    • Normal indeksler, basit ve belirli sütunlarda çok hızlı arama imkanı sağlarken; Full-Text Index, büyük metin ve veri kümelerinde hızlı sonuçlar verir.
  • Full-Text Catalogs: Bir veya daha fazla full-text index içeren mantıksal kapsayıcıdır buna şu şekilde örnek verebiliriz, aynı veri tabanı altında bulunan Icerik tablosunda bulunan “aciklama” kolonu ile musteri tablosunda bulunan “adres” kolonuna full-text index attığımız senaryoda iki tablonun ortak bir catalog kullanabilir.
  • Full-Text Queries: SQL Server ‘da full-text search yapmak için özel olarak kullanılan sorgulardır. Bu sorgular,

NOT: Full-text search işlemlerinde LIKE ile arama yaparken kullanılan ‘%’ operatörü yerine ‘*’ operatörünü kullanabilirsiniz.

  • Contains:
SELECT *
FROM Icerik
WHERE CONTAINS (aciklama, 'elma')
--aciklama kolonunda elma geçen tüm kayıtları getirir.

SELECT *
FROM Icerik
WHERE CONTAINS (aciklama, '*ma')
--aciklama kolonunda "ma" ile biten tüm kayıtları getirir.

SELECT *
FROM Icerik
WHERE CONTAINS (aciklama, 'elma AND kırmızı')
--aciklama kolonunda elma ve kırmızı geçen tüm kayıtları getirir.
  • Containstable: Daha kesin ve kontrollü arama sonuçları ile daha az sayıda arama sonucu ile hassas arama sonuçları getirir.
SELECT *
FROM Icerik
WHERE CONTAINSTABLE (aciklama, 'elma')
--aciklama kolonunda tam olarak "elma" geçen tüm kayıtları getirir.

SELECT *
FROM Icerik
WHERE CONTAINSTABLE (aciklama, 'kırmızı elma')
--aciklama kolonunda tam olarak "kırmızı elma" geçen tüm kayıtları getirir.

 

  • Freetext: Esnek bir yapıda olup diğer full-text search arama yöntemlerine göre daha fazla sayıda sonuç döndürürken daha alakasız sonuçlarda getirebilir.
SELECT *
FROM Icerik
WHERE CONTAINS (aciklama, FREETEXT('kırmızı elma')) 
--aciklama kolonunda "kırmızı" ve "elma" geçen tüm kayıtları getirir.

 

Bu sorgu fonksiyonları kapsamında çok daha detayları var ancak ben daha fazla detaya girmeyeceğim.

 

Full-Text Search Avantajları

  • Büyük metin verileri üzerinde ortama bağlı olarak yüksek hız ve performans sağlayabilir.
  • Kelime, kelime grubu veya tüm cümle üzerinde arama yapabilme esneklik imkanı sunar.
  • Freetext ile eş anlamlı ve yakınsama gibi gelişmiş arama özellikleri sunar.

Full-Text Search Dezavantajları

  • Full-text indexler, ek depolama alanı gerektirir.
  • Full-text indexler, oluşturulması ve güncellemesi zaman alabilir ve yönetimi (DBA) olarak daha karmaşık olabilir.
  • Bazı dillerde bulunan karakter ve karakter setlerinde tam arama desteği sınırlı olabilir.

 

Full-Text Index Oluşturma Adımları

1) İlk adım kataloğumuzu oluşturuyoruz, Catalog ismi belirlendikten sonra owner belirliyoruz. Sensitive ya da Insensitive kendi ortam şartlarınıza göre karar vermelisiniz.

Görsel – 2

2) Şimdi stoplist oluşturacağız ama öncesinde açıklayalım, belirli kelimelerin full-text indexleme ve arama işlemleri sırasında göz ardı edilmesini sağlayan listemizdir. Stoplist genellikle çok sık kullanılan ancak arama sonuçlarında anlamlı bir bilgi taşımayan kelimelerdir; stoplist ismi belirlendikten sonra owner belirliyoruz.

Görsel – 3

 

Görsel – 4

 

Stoplist oluşturulduktan sonra kelime ekleme işlemini gerçekleştirebilirsiniz.

 

Görsel – 5

 

3) İlgili tabloya gelerek sağ tıklayarak Full-Text Index > Define Full-Text Index… ‘e tıklıyoruz.

Görsel – 6

 

Karşımıza gelen ekran next diyerek ilerliyoruz ve Görsel – 8 ‘de olduğu gibi unique bir index seçmemiz gereken ekran ile karşılaşıyoruz bu kısmı makalenin girişinde detaylı bir şekilde bahsetmiştim, oluşturmuş olduğumuz unique index seçerek next diyerek ilerliyoruz.

Görsel – 7

 

Görsel – 8

 

4) Şimdi karşımıza gelecek olan ekranda ilgili tablonun kolonlarından hangilerine full-text index oluşturmak istiyoruz bunları belirliyoruz. Bu kısımda tek bir kolon seçmek veya birden fazla kolon seçmek zorunluluğu gibi bir durum yok sizin ihtiyaçlarınız dahilinde seçebilirsiniz.

Görsel – 9

5) Şimdi karşımıza gelecek olan ekrandaki seçenekleri bir örnek ile açıklamak isterim, siz tablonuza verileri aktardınız ve sonrasında da full-text index oluşturdunuz sonrasında ilgili tabloya veriler gelmeye devam etti o zaman nasıl bir senaryo ile ilerleyelim diye soruyor yani 1. seçenek olan ben gelen verileri otomatik olarak mı aktarılacak ? Seçenek manuel yani kendin el ile mi yapacaksın ? 3. Seçenek yoksa artık gelecek olan verileri full-text index ‘e atmak istemiyor musun ? Bu işlemi ileri ki adımlarda da population olarak tekrar üzerinden geçeceğiz. Ben otomatik seçerek next diyerek ilerliyorum, sizde burada ortam ve ihtiyaçlarınıza yönelik tercihinizi yapabilirsiniz.

Görsel – 10

 

6) Karşımıza gelecek olan ekranda, Catalog seçimi yukarıda oluşturmuş olduğumuz “select full-text catalog“ kısmından seçiyoruz. “select full-text stoplist” kısmından yukarıda oluşturmuş olduğumuz stoplistimizi seçiyoruz. “select index filegroup” kısmı ise oluşturacağımız index ‘in konumunu belirliyoruz, ilgili veri tabanında indeksleriniz için kullandığınız farklı bir filegroup var ise indeksinizi oraya konumlandırabilirsiniz veya girişte bahsetmiş olduğum senaryolardan biri olan partition filegroup kısmına da konumlandırabilirsiniz.

Burada “Create a new catalog” seçeneği ile yeni bir catalog da oluşturabilirsiniz ancak biz zaten bir catalog oluşturduğumuz için buna gerek duymadık.

Görsel – 11

Görsel – 12

Finish diyerek full-text index kurulumunu tamamlamış oluyoruz.

7) Şimdi full-text index oluştu ancak süreç henüz bitmedi, İlgili veri tabanının altından Storage > Full Text Catalogs şeklinde ilerlediğinizde ilk aşamada catalog oluşturduğunuzda Görsel – 13 ‘teki gibi “Catalog Size”, “Item Count” (tablonuzdaki row count eşit olacaktır) ve “Unique Key Count” boş görünürken şimdi ilgili tabloda full-text index oluşturma adımlarını tamamlayıp cataloga bağladığınızda Görsel – 14 ‘teki gibi değerleriniz değişmeye başlayacaktır. Burada unutulmaması gereken şeylerden birisi de bu adım veri tabanınızın transaction log file boyutunu artıracak buna bağlı olarak transaction log backup boyutu da büyüyecektir.

Görsel – 13

 

Görsel – 14

 

Dikkat edilmesi gereken bir diğer konu ise full-text index oluşturmak istediğiniz tablonuzda population işlemi tamamlanmadan sorgulamalara geçerseniz hatalı veya eksik sonuçlar alabilirsiniz, bunu bir örnek ile açıklamak istiyorum; örnek veriyorum sizin full-text index oluşturmak istediğiniz tablonuzun row count 1000000 olsun ilk 6 adımda bahsettiğimiz şekilde full-text index oluşturma aşamalarını tamamladınız Storage > Full Text Catalogs şeklinde ilerleyerek ilgili catalogu kontrol ettiğinizde ise population işleminin henüz bitmediğini gördünüz ancak “Item Count” değerinin 300000 olduğunu gördünüz bu durumda full-text index referans aldığı unique indexin 300000 değerini catalogda population tamamlanmışken henüz geriye kalan veriler dahil değildir o yüzden sorgunuzda hatalı veya eksik sonuçlar alabilirsiniz.

Population işlemi tablonuzun boyutu, row count ve full-text index atılan kolonlarda bulunan verilere göre değişkenlik gösterecektir. Bu süreçte yukarda da bahsettiğim gibi transaction log file ve transaction log backup boyutunuzu göz önünde bulundurmanız gerekmektedir.  Population işlemi tamamlandığında Görsel – 15 ‘te örnekteki gibi görüntüleyebilir ve sonuç olarak çıktısı hakkında bilgi verecektir.

Görsel – 15

Bu makalede Full-text index oluşturulması adımları ve örnekleri üzerinde durmaya çalıştım, bir sonraki makalede görüşünceye dek iyi ki varsınız, sevgiler 🙂

 

 

 

Benzer Yazılar

MSSQL Server ColumnStore Index

SQL Server 2 gün önce

SQL Server’da Columnstore Index  Arşivlenmiş veriler, büyük veri ambarları ve analitik sorgular için performansı artıran bir index türüdür. Geleneksel satır tabanlı Index yerine, verileri sütunlar halinde depolar ve sorgu performansını önemli ölçüde artırır.İçindekiler1.Depolama Yapısı2. Sıkıştırma3. Veri Erişimi4. Batch Mode Processing5. Delta Store6. Veri Güncelleme ve Silme7. RowgroupSQL Server Arkaplanında gerçekleşen işlemleri ve çalışma mantığını inceleyelim.1.Tuple Mover2.Index Rebuild ve Reorganize3.Garbage Collection4.Compression5.Delta Store Management6.Segment MergingColumnstore Index’in Bellek Kullanımı1.Segment Bellek Kullanımı2.Batch Mode Processing’in ÖzellikleriColumnstore Index’in Başlıca Dezavantajları1.Güncelleme ve Silme İşlemlerinin Karmaşıklığı2.Satır Tabanlı İşlemler İçin Uygun Olmaması3.Sınırlamalar ve UyumsuzluklarSürümlere göre ColomStore Index özellikleriSQL Server 2012’de Columnstore Index’in Temel ÖzellikleriSQL Server 2014’de Columnstore Index’in Temel ÖzellikleriSQL Server 2016’de Columnstore Index’in Temel ÖzellikleriColumnstore index aşağıdakilerle birlikte kullanılamaz. MSSQL Server ‘da ColumnStore İndex ‘in Avantajları, Kullanım alanları , Sürümlere göre farkları , Performansa etkisi ve en önemlisi Data boyutunuzdaki Compress(Sıkıştırma) özelliğini nasıl kullandığını anlatmaya çalışacağım. İlk olarak SQL Server 2012‘de tanıtılan Columnstore Index, sütun bazında depolanan İndex’in […]

MSSQL Server Tablodaki Kolonun Yerini Değiştirme

SQL Server 2 gün önce

SQL Server’da tablo oluştururken kolonu yanlış konumlandırdım diyenlerdenseniz doğru yazıyı okuyorsunuz. SQL Server’da bir tablo oluştururken kolonların sırasını yanlış konumlandırabilirsiniz. Ancak, kolonların sırasını değiştirmek oldukça kolaydır ve aşağıdaki örnek olarak yaptığım adımları izleyerek bunu yapabilirsiniz. Test Veritabanımızın altında bulunan “kisiler” tablosunu oluştururken sırasıyla “ad” ve “soyad” kolonlarını oluşturmak isterken önce “soyad” ardından “ad” kolonunu yazdığınızı varsayalım. Tabloyu silip tekrar oluşturmak veya kolon isimlerini değiştirmekte bir yöntemdir ancak burada içerisine çok sayıda veri yazılan bir tablo olduğunu varsayarak başka bir yöntem kullanacağım. Değişiklik yapmak istediğimiz tabloda “design” kısmına giriyoruz. Görüldüğü üzere “soyad” kolonu önce “ad” kolonu sonra yazılmış. Bunların yerini değiştirmek için “ad” veya “soyad” yazan yerlerin hemen sol tarafında bulunan ok işaretinden istediğimiz yere sürükleyip bırakıyoruz. Yapmamız gereken tek işlem bu aslında.   Görüldüğü üzere kolonların yerleri değişti. Bu noktada yapılan işlemi kaydetmemiz lazım. Değişiklikleri kaydettiğimizde karşımıza aşağıdaki hata gelmektedir. Hata Mesajı: Değişikliklerin kaydedilmesine izin verilmez. Yaptığınız değişiklik aşağıdaki tablonun […]

Failover Cluster Yapısına Quorum Disk Witness Ekleme

SQL Server 4 hafta önce

Bu makalede mevcut Failover Cluster yapısı kurulu olan bir sistemde Quorum Disk Witness ekleme işlemini ele alacağız. Quorum disk kurulumuna geçmeden önce Quorum yapısını ne olduğundan bahsedeyim. Mevcut clusterımızın ayakta kalması için cluster database dosyalarının tutulduğu kısımdır. Bu yapının çalışabilmesi için cluster yapımızda bulunan toplam sunucuların yarısından bir fazla ile sunucu oy hakkına sahip olur ve lider olarak ayağa kalkması sağlanır. Bu yapımız olmasa da olur aslında bir sıkıntı yaşamayız. Sorun şurada karşımıza çıkar iki sunucu aynı anda down oldu ve iki sunucu aynı milisaniye zamanında ayağa kalkmaya çalışırsa mevcut sistemimizin çalışmamasına sebep olur. Bu riskin önüne geçmek için Quorum yapısı oluşturulur. Bu yapılandırmayı yapmasak ta mevcut bir sunucu down olduğunda diğer sunucu up olmaktadır. Genellikle çift yapılı node durumların oluşturulur. İki farklı quorum modeli bulunmaktadır. Bunlar Disk Witness yapısı diğeri ise File Shared Witness yapısıdır. Herhangi bir failover cluster yapısı oluşturduğumuzda cluster’daki sunucularımın göreceği ortak bir iSCSI disk yapılandırılması […]

0 Yorum

Yorum Yaz

Rastgele