ORACLE TEXT (CONTEXT INDEX) SQL – 1

Oracle Veritabanında az bilinen ve kullanılan fakat oldukça önemli ve kullanışlı bir özellik olan Oracle Text özelliğinden en genel haliyle bahsecek olursak; Belli bir formattaki bütünleşik yazılar içerisinde sorgulama yapmaya yarayan bir araçtır. Oracle TEXT gelişmiş bir indexleme mekanizması kullanarak plain text (Varchar2, CLOB) veya BINARY dosyalar (BLOB) üzerinde arama ve görüntüleme işlevleri sunmaktadır ayrıca Veritabanında saklanan metinlerin içeriklerinin indekslenmesi (herhangi bir directorydeki dökümanlarda dahil) ve bu indekslerin üzerinden içeriğe dayalı gelişmiş sorgulamalar yapılmasına olanak verir. (MS Office, Adobe PDF, HTML ve XML gibi 150 döküman tipini ve 40’tan fazla dili destekler). Genelde Döküman veya yazılardaki geçen kelimeleri bulmak, döküman katalogları oluşturmak gibi amaçlar için kullanılmaktadır. Oracle Text web de, veritabanında ya da dosyalardaki dökümanları analiz, search, indexlemelerinde standart sql kullanır. Oracle text kullanılırken textin kullanılcağı kolonun uygun indeks seçilerek indexlenmelidir. Oracle text i oluşturuken hangi tipte sorgu kullanacağınız index tipinin seçimi için önemlidir.

Full Text İndex Çeşitleri:

  • CONTEXT Indexes
  • CTXCAT Indexes
  • CTXRULE Indexes
  • Context Indexes : Bu index çok yüksek miktarda text içeren datalar için kullanılır. Örneğin pdf,word,html gibi datalar için.
  • CtxCat Indexes : Bu index contexte göre daha az olan text alanlar için kullanılır. Örneğin varchar2 tipinde bir field için bu index kullanılabilir. CtxCat index için synchronize işlemi gerekmez bu otomatik olarak yapılır.
  • CtxRule Indexes : Daha çok document based indexlerde kullanılır.

(Katnak : https://docs.oracle.com/database/121/CCAPP/toc.htm ) Full Text Index te Kullanılan Terimler Ve Açıklamaları

Phrase Search : Arama yapılırken kelimenin tamamını aramak için kullanılır. Örneğin “Faruk Çevik” kelimelerini bir bütün olarak aramak gibi. Google da aranacak kelimenin başına ve sonuna ” işareti konulduğunda da Phrase search şeklinde arama yapar.

Proximity SearchAranılan kelimeler arasındaki yakınlığa göre arama şeklidir.

Wildcard Search : Arama yapılırken herhangi bir karakter anlamına gelen bir karakter yazılarak yapılan aramadır. Sql de Like ile yapılan aramalarda  “%” karakteri ve “_” karakterini kullanarak arama yapma wilcard searche örnektir wilcard search aramayı yavaşlatıcı bir yöntemdir.

Full Text Index İşlemleriCreate Index : 

Create Index index_name ON table_name(field_name) INDEXTYPE IS CTXSTS.CONTEXT 

En basit haliyle bir tablodaki bir columna  yukarıdaki sql ifadesi ile index atılır. Bu sql tablo satır sayısı ve text ifadenin boyutuna göre oldukça uzun bir süre alabilir. O yüzden bu sql çalışmazdan önce bu tablonun sql bitene kadar lock olacağını hesaba katarak sql in execute edilmesi gerekmektedir. Ful Text Index atılmamış bir alanda sorgulama yaparken sorgumuz genel haliyele şu şekilde olurdu

SELECT * FROM Table_name WHERE Field_Name LIKE '%FARUK%' 

Full Text Index verildikten sonraki sorgu sql imiz ise aşağıdaki gibi olmalıdır;

 SELECT * FROM Table_Name WHERE CONTAINS(Field_Name,'FARUK' )>0 

 Score

Score dökümanlar içerisinde aradığınız kelimenin ters sıklığını belirten bir programlama algoritmasının sonucudur. Oracle bu algoritmada Salton formülü yöntemini kullanmıştır. Skorun yüksek olması için arama yaptığımız kelime veya cümlenin aradığımız kaynakta yüksek sıklıkta geçiyor olması ve diğer kaynaklarda da düşük sıklıkta geçiyor olması gerekmektedir. Ayrıntılı bilgi için Kaynak  Full Text Index Elementleri Multi Column Data Store   Bir Tabloda birden fazla alanda arama yapmak istiyorsak bu elementin kullanılması gerekmektedir. Örneğin TabloX adında bir tablomuz olsun bu tabloda baslik, aciklama, kisi, detay adlarında alanları olsun. Aramayı ise bu alanların hepsinde yapmak isteyelim. Öncelikle my_data_store adında bir preference oluşturuyoruz.

begin       
    ctx_ddl.create_preference(preference_name => 'my_data_store',
                        object_name => 'MULTI_COLUMN_DATASTORE');
end;
/

MULTI_COLUMN_DATASTORE preferencesi olan my_data_store da arama yapılcak olan alanları attribute olarak ekliyoruz.

begin
ctx_ddl.set_attribute(
    preference_name => 'my_data_store',
    attribute_name =>'COLUMNS',
    attribute_value => 'UYE_ID, TCNO, ADSOYAD, EPOSTA, TELNO, UYEKAYITIP');
end; 

Multi_column_datastore preferencesi olan multi_data ya arama yapılcak olan alanları attribute olarak ekliyoruz.

Sonrasında ise bu alanlardan nasıl sorgu yapacağımız kısmına geliyoruz, Bunun için bir Field oluşturmamız gerekiyor ve bu fielda index vereceğiz. Oluşturacağımız field sanal bir field olabilir veya aşağıdaki gibi tabloya bir field da ekleyebiliriz.

alter table TabloX add fti varchar2(1);

Bu tabloyu indexlemek için ise aşağıdaki sql çalıştırılır:

create index fti_Contex_text on TabloX(fti) indextype is ctxsys.context
parameters('DATASTORE my_data_store');

artık alanlarımız indexlenmiştir ve sorguya hazırdır Örnek bir sorgu yazalım;

select * from TabloX where CONTAINS(f_text,'[email protected]%')>0;

yukarıdaki sorgu indexlenmiş alanlarda farukcevik kelimesini aradı ve sonuçları bize getirdi.

ÖNEMLİ NOT: Burada çok önemli bir sorun var aslında, tabloya yeni kayıtlar geldiğinde veya tablo güncellendiğinde indexlerimiz eski tablonun indexleri olarak kalacaktır ve indexlerin yeniden düzenlenmesi gerekmektedir buna index syncronization denir ve burada Oracle indexi otomatik syncronization yapmayacaktır. Bunun için tablonuzun büyüklüğü, update ve insert sıklığı ve sizin bu sorguya ne aralıklarla ihtiyaç duyduğunuz gibi  parametreleri göz önüne alarak bunu job, trigger ve schedule ederek index syncronization işlemini yaptırmamız gerekmektedir. Indexin syncroniztaion problemi için çözüm yollarından birkaçına bakarsak ilk olarak Synch On(Commit)  kullanırsak her commit işleminden sonra full text indeximiz synchronize olacaktır.

CREATE INDEX fti_Contex_text ON TabloX(fti) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('datastore my_data_store sync(on commit)'); 

Synchronization için diğer bir yol Trigger yazmak olacaktır

create or replace trigger sync_text_trigger before insert or update or delete on TabloX BEGIN  ctxsys.ctx_ddl.sync_index('fti_Contex_text');
END sync_text_trigger; 

Synchronization için başka bir yolsa Oracle Job’s a eklmektir. Oracle Cloud Control üzerinden jobs kısmına indexin hangi zamanlarda synchronize olacağını ayarlarız. Tablomuz büyük ve performans bizim için önemli ise en iyi yöntem bu yöntemdir. Toplu olarak belli peryotarda indeximiz synchronize olacaktır.

exec ctx_ddl.sync_index('fti_Contex_text')LEXER.....