Oracle Text Nedir
Merhaba,
Bu yazıda Oracle veritabanının az bilinen ama işlevi büyük olan Text özelliğinden bahsedeceğim.
En kaba tanımı ile Oracle Text belli bir formatta olan bütünleşik yazılar içerisinde sorgulama yapmaya yarayan bir araçtır. Oracle Text gelişmiş bir INDEX’leme mekanizması kullanarak plain text (VARCHAR2, CLOB) veya BINARY dosyalar (BLOB) üzerinde arama ve görüntüleme işlevleri sunmaktadır.
Oracle Text en basit haliyle iki amaç için kullanılabilinir;
- Dokümanlarda veya yazılarda geçen anahtar kelimeleri veya cümleleri sorgulamak
- Doküman katalogları oluşturmak
Bu yazıda dokümanlarda geçen anahtar kelimeleri veya cümleleri sorgulamak ile ilgili anlatım yapacağım.
Oracle Text kullanmak istediğiniz veritabanı sütunu üzerinde oluşturulan bir INDEX ile işlevini yapmaktadır. Temel olarak kullanım amaçlarına göre 3 çeşit INDEX tipi mevcuttur. Her birinin SQL Query Keyword’u farklıdır. Hangi INDEX’i ne tür durumlarda kullanıldığını anlamak için aşağıdaki link’i veya tabloyu inceleyebilirsiniz;
http://docs.oracle.com/cd/B10501_01/text.920/a96517/cdefault.htm
| Type Of Index | Description | Query Operator |
|---|---|---|
| CONTEXT | Use this index to build a text retrieval application when your text consists of large coherent documents. You can index documents of different formats such as Microsoft Word, HTML, XML, or plain text.You can customize your index in a variety of ways. | CONTAINS |
| CTXCAT | Use this index type to improve mixed query performance. Suitable for querying small text fragments with structured criteria like dates, item names, and prices that are stored across columns. | CATSEARCH |
| CTXRULE | Use to build a document classification application. You create this index on a table of queries, where each query has a classification. Single documents (plain text, HTML, or XML) can be classified by using the MATCHES operator. | MATCHES |
Score Kavramı
Score dokümanlar içerisinde aradığınız anahtar kelimenin ters sıklığını belirten bir puanlama algoritmasının sonucudur. Oracle bu algoritmada Salton formülü yöntemini kullanmıştır.
Skorun yüksek olması için arama yaptığınız anahtar kelime veya cümlenin bir doküman içerisinde yüksek sıklıkla geçiyor olması aynı zamanda tablodaki bütün dokümanlar içerisinde düşük sıklıkla geçiyor olması gerekmektedir.
Daha fazla bilgi için;
http://docs.oracle.com/cd/A91202_01/901_doc/text.901/a90121/ascore2.htm
http://en.wikipedia.org/wiki/Gerard_Salton
Örnekler
Örnekler için öncelikle birkaç setup adımı yapmamız gerekmektedir.
- Örnekte kullanılacak doküman örneklerinin belirlenmesi ve geliştirmenin yapılacağı sistemin çalıştığı makinede erişim olan bir dizine kopyalanması.
- Dizinin karşılığına denk gelen DIRECTORY objesinin veritabanında oluşturulması
- BLOB bir alan içeren custom bir tablo ve sequence objelerinin oluşturulması
- Dosyaları aktaran genel bir pl/sql prosedürü oluşturulması. Bkz: PL/SQL File to Blob Yazısı
- Prosedürün doküman sayınız kadar çalıştırılması
1. Dokümanlar belirlenir.
2. DIRECTORY objesi oluşturulur.
|
1 2 3 |
create or replace directory ANIL_BLOG_DIRECTORY1 as 'C:\Users\Anil\Desktop\BLOG\OracleText\files'; |
3. TABLE ve SEQUENCE objeleri oluşturulur.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
create table oracle_text_table ( sira_id number, dokuman blob, dokuman_ismi varchar2(300), dokuman_uzanti varchar2(10), kayit_tarihi date ); create sequence anil.oracle_text_table_s start with 1 increment by 1 minvalue 1 nocache nocycle noorder; |
4. PROCEDURE oluşturulur.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
create or replace procedure dosya_yukle (p_dosya_adi in varchar2, p_dizin in varchar2) is l_blob_dosya blob; l_dosya bfile := bfilename(p_dizin, p_dosya_adi); l_uzanti varchar2(4) := substr(p_dosya_adi, instr(p_dosya_adi, '.')+1); begin insert into oracle_text_table ( sira_id, dokuman, dokuman_ismi, dokuman_uzanti, kayit_tarihi ) values ( oracle_text_table_s.nextval, empty_blob(), p_dosya_adi, l_uzanti, sysdate ) returning dokuman into l_blob_dosya; dbms_lob.open(l_dosya, dbms_lob.lob_readonly); dbms_lob.open(l_blob_dosya, dbms_lob.lob_readwrite); dbms_lob.loadfromfile(dest_lob => l_blob_dosya, src_lob => l_dosya, amount => dbms_lob.getlength(l_dosya)); dbms_lob.close(l_dosya); dbms_lob.close(l_blob_dosya); commit; end; |
5. Dosyalar tabloya aktarılır.
|
1 2 3 4 5 6 7 8 9 10 |
declare l_dizin varchar2(30) := 'ANIL_BLOG_DIRECTORY1'; begin dosya_yukle('c_tutorial.doc',l_dizin); dosya_yukle('cpp.pdf',l_dizin); dosya_yukle('java_tutorial.pdf',l_dizin); dosya_yukle('plsql.html',l_dizin); end; |
Kurulum işlemleri tamamlandıktan sonra Oracle Text için tablonun DOKUMAN sütunu üzerine bir adet CONTEXT INDEX oluşturulur.
|
1 2 3 |
CREATE INDEX oracle_text_table_ctx_idx ON oracle_text_table (dokuman) INDEXTYPE IS CTXSYS.CONTEXT; |
Tablo istatiskleri toplanır.
|
1 2 3 |
EXEC DBMS_STATS.GATHER_TABLE_STATS('ANIL', 'oracle_text_table', cascade=>TRUE); |
Yukarıdaki işlemler yapıldıktan sonra dokümanlar üzerinde sorgulama yapabilirsiniz. Tabloda CONTEXT IDX kullandığımız için SQL sorgunuzun WHERE koşulunda “CONTAINS(alan_adi, <keyword>) > 0” koşulunu çalıştırmanız gerekmektedir. Aşağıdaki bir takım sorgular ve sonuçlarını bulabilirsiniz.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'A C program contains ', 1) > 0 ORDER BY SCORE (1) DESC; SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'Java', 1) > 0 ORDER BY SCORE (1) DESC; SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'C++ Language Tutorial', 1) > 0 ORDER BY SCORE (1) DESC; SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'Pointers and arrays', 1) > 0 ORDER BY SCORE (1) DESC; SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'IF value LIKE pattern THEN', 1) > 0 ORDER BY SCORE (1) DESC; SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'printf', 1) > 0 ORDER BY SCORE (1) DESC; |
Sonuçlar:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
SCORE SIRA_ID ---------- ---------- DOKUMAN_ISMI -------------------------------------------------------------------------------- 3 10 c_tutorial.doc 1 row selected. SCORE SIRA_ID ---------- ---------- DOKUMAN_ISMI -------------------------------------------------------------------------------- 100 12 java_tutorial.pdf 12 13 plsql.html 2 rows selected. SCORE SIRA_ID ---------- ---------- DOKUMAN_ISMI -------------------------------------------------------------------------------- 3 11 cpp.pdf 3 10 c_tutorial.doc 2 rows selected. SCORE SIRA_ID ---------- ---------- DOKUMAN_ISMI -------------------------------------------------------------------------------- 100 11 cpp.pdf 24 10 c_tutorial.doc 2 rows selected. SCORE SIRA_ID ---------- ---------- DOKUMAN_ISMI -------------------------------------------------------------------------------- 4 13 plsql.html 1 row selected. SCORE SIRA_ID ---------- ---------- DOKUMAN_ISMI -------------------------------------------------------------------------------- 100 10 c_tutorial.doc 1 row selected. |
INDEX Tanımı
Oluşturulan CTX Index için bilgileri çekmek için aşağıdaki SQL sorgusunu çalıştırabilirsiniz;
|
1 2 3 |
select ctx_report.describe_index('oracle_text_table_ctx_idx') from dual; |
Sonuç:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 |
=========================================================================== INDEX DESCRIPTION =========================================================================== index name: "ANIL"."ORACLE_TEXT_TABLE_CTX_IDX" index id: 1063 index type: context base table: "ANIL"."ORACLE_TEXT_TABLE" primary key column: text column: DOKUMAN text column type: BLOB language column: format column: charset column: =========================================================================== INDEX OBJECTS =========================================================================== datastore: DIRECT_DATASTORE filter: AUTO_FILTER section group: HTML_SECTION_GROUP lexer: BASIC_LEXER wordlist: BASIC_WORDLIST stemmer: ENGLISH fuzzy_match: GENERIC stoplist: BASIC_STOPLIST stop_word: these stop_word: they stop_word: this stop_word: those stop_word: though stop_word: through stop_word: thus stop_word: to stop_word: too stop_word: until stop_word: ve stop_word: very stop_word: was stop_word: we stop_word: were stop_word: what stop_word: when stop_word: where stop_word: whether stop_word: which stop_word: while stop_word: who stop_word: whose stop_word: why stop_word: will stop_word: with stop_word: would stop_word: yet stop_word: you stop_word: your stop_word: yours stop_word: Mr stop_word: Mrs stop_word: Ms stop_word: a stop_word: all stop_word: almost stop_word: also stop_word: although stop_word: an stop_word: and stop_word: any stop_word: are stop_word: as stop_word: at stop_word: be stop_word: because stop_word: been stop_word: both stop_word: but stop_word: by stop_word: can stop_word: could stop_word: d stop_word: did stop_word: do stop_word: does stop_word: either stop_word: for stop_word: from stop_word: had stop_word: has stop_word: have stop_word: having stop_word: he stop_word: her stop_word: here stop_word: hers stop_word: him stop_word: his stop_word: how stop_word: however stop_word: i stop_word: if stop_word: in stop_word: into stop_word: is stop_word: it stop_word: its stop_word: just stop_word: ll stop_word: me stop_word: might stop_word: my stop_word: no stop_word: non stop_word: nor stop_word: not stop_word: of stop_word: on stop_word: one stop_word: only stop_word: onto stop_word: or stop_word: our stop_word: ours stop_word: s stop_word: shall stop_word: she stop_word: should stop_word: since stop_word: so stop_word: some stop_word: still stop_word: such stop_word: t stop_word: than stop_word: that stop_word: the stop_word: their stop_word: them stop_word: then stop_word: there stop_word: therefore storage: BASIC_STORAGE r_table_clause: lob (data) store as (cache) i_index_clause: compress 2 |
Yukarıdaki sonuçta dikkat edilmesi gereken noktalardan biri stop_word bölümüdür.
stop_word
STOPWORD’ler arama indexlemenin durduğu keywordlerdir. STOPWORD’ler STOPLIST’lerin içinde yer almaktadır. INDEX’ler oluşturulurken herhangi bir parametre ile belirtilmemişse INDEX’in ön değer STOPLIST değeri “DEFAULT_STOPLIST” dir.
Bu listeler ve yasak kelimeler ile ilgili aşağıdaki iki tablodan yararlanabilirsiniz.
|
1 2 3 4 5 |
select * from ctx_stoplists; select * from ctx_stopwords; |
Aşağıda yasak kelime listesinde bulunan “you” anahtar kelimesi ile ilgil bir SQL çalıştırıldığında, anahtar kelime dokümanlarda bolca bulunmasına rağmen sonuç alınmamaktadır.
|
1 2 3 4 5 6 |
SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'you', 1) > 0 ORDER BY SCORE (1) DESC; |
Oracle bize bu yasaklı kelimelere ve listelere müdahale edebilmemiz için ctx_ddl paketini sunmuştur. “you” kelimesini listeden çıkarmak için aşağıdaki script çalıştırılır.
|
1 2 3 4 5 |
begin ctx_ddl.remove_stopword('CTXSYS.DEFAULT_STOPLIST','you'); end; |
Değişikliğin yansıması için INDEX drop edilir ve tekrar yaratılır.
|
1 2 3 4 5 |
drop index oracle_text_table_ctx_idx; create index oracle_text_table_ctx_idx on oracle_text_table (dokuman) indextype is ctxsys.context; |
Sorgu tekrar çalıştırılır ve bu sefer 4 dokümanın da geldiği görülür.
|
1 2 3 4 5 6 |
SELECT SCORE (1) score, sira_id, dokuman_ismi FROM oracle_text_table WHERE CONTAINS (dokuman, 'you', 1) > 0 ORDER BY SCORE (1) DESC; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SCORE SIRA_ID ---------- ---------- DOKUMAN_ISMI -------------------------------------------------------------------------------- 100 11 cpp.pdf 100 13 plsql.html 93 12 java_tutorial.pdf 78 10 c_tutorial.doc 4 rows selected. |
Son olarak tablonuza veri ekleme, çıkarma ve güncelleme yaptıkta ilgili CTX INDEX kendisini diğer INDEX’ler gibi senkronize etmez. Performansı dengede tutmak için bunu aralıklarla sizin yapmanız gerekmektedir. Bunun için aşağıdaki script’i çalıştırınız.
|
1 2 3 |
EXEC CTX_DDL.SYNC_INDEX('oracle_text_table_ctx_idx'); |