Oracle Text Çalışırken tuttuğum bazı notları ve örnekleri burada paylaşmak istedim. Örneklerin bazıları için değişik kaynaklardan faydalandım, bazılarını ise kendim yazdım. Umarım faydalı olur.
1 -)Full Text Search Nedir?Indexlenmek istenen alan eğer text içeriyorsa ki bu bir html dosyası pdf de olabilir, bu tip verileri indexlemek için Full Text Search kullanılır. Bu şekilde büyük metin verileri üzerinde daha hızlı sorgulama yapılabilir.
2 -) Oracle Text Index Tipleri2.1 -) CONTEXT: Arayacağınız metin büyük text verileri içeriyorsa (HTML,word,pdf,xml de olabilir) bu tip indeksleme kullanılır.
CONTAINS sözcüğü ile sorgulama yapılır. Veri değiştiğinde (DML sonrası) index
CTX_DDL.SYNC_INDEX ile güncellenmelidir. Bölümlenmiş (partitioned) tabloları destekler.
2.2 -) CTXCAT: Daha küçük documan ve metinler üzerinde arama yapmak için kullanılır. Mixed Query'lerde daha iyi performans sağlar. Indeks'e text olmayan tablo sütunları da dahil edilebilir ve böylece performans arttırımı sağlanmış olur.
CATSEARCH sözcüğü ile sorgulama yapılır. Index oluşturmak CONTEXT'den daha fazla zaman alır ve daha çok yer kaplar. Bölümlenmiş (partitioned) tabloları desteklemez.
2.3 -) CTXRULE : Daha çok döküman sınıflandırılması uygulamalarında kullanılır.
MATCHES sözcüğü ile sorgulama yapılır.
2.4 -) CTXXPATH : Eskiye uyum için XMLType tipindeki sütunlar üzerindeki
existsNode() sorgulamalarını hızlandırmak için eklenmiştir. Yeni uygulamalar için
XMLIndex kullanılması önerilir.
ÖRNEK #1: Kullanıcı oluşturma ve haklar-- Connect as the System Admin
CONNECT sys;
-- Create User
CREATE USER myuser IDENTIFIED BY myuser_password;
-- Grant Roles
GRANT RESOURCE, CONNECT, CTXAPP TO MYUSER;
-- Grant to MYUSER execute privileges on all Oracle Text packages
GRANT EXECUTE ON CTX_CLS TO myuser;
GRANT EXECUTE ON CTX_DDL TO myuser;
GRANT EXECUTE ON CTX_DOC TO myuser;
GRANT EXECUTE ON CTX_OUTPUT TO myuser;
GRANT EXECUTE ON CTX_QUERY TO myuser;
GRANT EXECUTE ON CTX_REPORT TO myuser;
GRANT EXECUTE ON CTX_THES TO myuser;
-- Connect as the New User
CONNECT myuser;
ÖRNEK #2: Context Index ile HTML üzerinde sorgulama
-- Create your text table
CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));
-- Load Documents into Table
INSERT INTO docs VALUES(1, 'California is a state in the US.');
INSERT INTO docs VALUES(2, 'Paris is a city in France.');
INSERT INTO docs VALUES(3, 'France is in Europe.');
-- Create the CONTEXT index
CREATE INDEX idx_docs ON docs(text)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
-- look for all documents that contain the word France:
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'France', 1) > 0;
-- add some rows
INSERT INTO docs VALUES(4, 'Los Angeles is a city in California.');
INSERT INTO docs VALUES(5, 'Mexico City is big.');
-- the index is not synchronized, these new rows are not returned with a query on city:
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;
-- synchronize the index with 2Mb of memory, and reexecute the query:
EXEC CTX_DDL.SYNC_INDEX('idx_docs', '2M');
ÖRNEK #3: Özel Karakterleri İndeksleme ( Örneğin, "*=" )-- Create your text table
CREATE TABLE mydata (id NUMBER PRIMARY KEY, text VARCHAR2(1000));
-- Load some data into Table
INSERT INTO mydata VALUES(1, '==================================');
INSERT INTO mydata VALUES(2, '444 ** *** ***** 444');
INSERT INTO mydata VALUES(3, '============== Cash ================');
INSERT INTO mydata VALUES(4, '============= Success ===============');
-- set up the special characters as printjoins if you wish to search for them
EXEC CTX_DDL.CREATE_PREFERENCE ('special_chars_lexer', 'BASIC_LEXER');
EXEC CTX_DDL.SET_ATTRIBUTE ('special_chars_lexer', 'PRINTJOINS', '*=');
-- create the CONTEXT index with your special_chars_lexer
CREATE INDEX idx_mydata ON mydata(text)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER special_chars_lexer');
-- make some query
SELECT SCORE(1), id, text FROM mydata WHERE CONTAINS(text, '%4%', 1) > 0 ORDER BY SCORE(1) DESC;
SELECT SCORE(1), id, text FROM mydata WHERE CONTAINS(text, '{444}', 1) > 0;
SELECT SCORE(1), id, text FROM mydata WHERE CONTAINS(text, '{44}', 1) > 0;
SELECT SCORE(1), id, text FROM mydata WHERE CONTAINS(text, '{**}', 1) > 0;
SELECT SCORE(1), id, text FROM mydata WHERE CONTAINS(text, '{============== Cash ================}', 1) > 0;
-- synchronize the index with 2Mb of memory, and reexecute the query:
EXEC CTX_DDL.SYNC_INDEX('idx_mydata', '2M');
ÖRNEK #4: -- Set up an auction table to store your inventory:
CREATE TABLE auction(
item_id NUMBER,
title VARCHAR2(100),
category_id NUMBER,
price NUMBER,
bid_close DATE
);
-- Populate the table with various items
INSERT INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, '24-11-2002');
INSERT INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, '25-11-2002');
INSERT INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, '26-11-2002');
INSERT INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, '27-11-2002');
INSERT INTO AUCTION VALUES(5, '5-STAR(*****) CAMERA', 1, 2500, '28-11-2002');
INSERT INTO AUCTION VALUES(6, '5-STAR (*****) CAMERA', 1, 2500, '28-11-2002');
INSERT INTO AUCTION VALUES(7, '5 - STAR (*****) CAMERA', 1, 2500, '28-11-2002');
INSERT INTO AUCTION VALUES(8, 'EQUAL (=) CAMERA', 1, 25, '29-11-2002');
INSERT INTO AUCTION VALUES(9, '* STAR CAMERA', 1, 2500, '28-11-2002');
INSERT INTO AUCTION VALUES(10, '=======================================', 1, 100, '28-11-2002');
INSERT INTO AUCTION VALUES(11, '444 ** *** ***** 444', 1, 101, '28-11-2002');
INSERT INTO AUCTION VALUES(12, '============== Cash ==================', 1, 102, '28-11-2002');
INSERT INTO AUCTION VALUES(13, '============= Success ================', 1, 103, '28-11-2002');
INSERT INTO AUCTION VALUES(14, '============== Cash ==================4', 1, 104, '28-11-2002');
INSERT INTO AUCTION VALUES(15, '- -- --- ---- Update ---- --- -- -', 1, 105, '28-11-2002');
-- set up the special characters as printjoins if you wish to search for them
EXEC CTX_DDL.CREATE_PREFERENCE ('special_chars_lexer', 'BASIC_LEXER');
EXEC CTX_DDL.SET_ATTRIBUTE ('special_chars_lexer', 'PRINTJOINS', '*=-');
-- create an index set and add a sub-index
EXEC CTX_DDL.CREATE_INDEX_SET('auction_iset');
EXEC CTX_DDL.ADD_INDEX('auction_iset','price');
-- Create the combined catalog index on the AUCTION table
DROP INDEX auction_titlex;
CREATE INDEX auction_titlex ON AUCTION(title)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('LEXER special_chars_lexer index set auction_iset');
-- Query
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'price <= 300')>0;
-- query for special character (=)
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '{=}', 'order by price') > 0;
SELECT title, price FROM auction WHERE CATSEARCH(title, '{=} & CAMERA', 'order by price')>0;
-- query for special character (-)
SELECT title, price FROM auction WHERE CATSEARCH(title, '{5-STAR}','order by price')>0; */
SELECT title, price FROM auction WHERE CATSEARCH(title, '\-','order by price')>0; -- /*not finds %-% */
SELECT title, price FROM auction WHERE CATSEARCH(title, '{*-*}','order by price')>0; /*-- finds %-% */
SELECT title, price FROM auction WHERE CATSEARCH(title, '{--}','order by price')>0; -- finds nothing
-- query for special character (*)
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '{*}', 'order by price') > 0; /* interpreted as wildcard */
SELECT title, price FROM auction WHERE CATSEARCH(title, '\*','order by price')>0; /* not finds %*% */
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '\*\*', 'order by price') > 0; -- not finds %**% */
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '{\*\*}', 'order by price') > 0; /* same as above */
SELECT title, price FROM auction WHERE CATSEARCH(title, '\*\*\*\*\*','order by price')>0; /* only ***** */
SELECT title, price FROM auction WHERE CATSEARCH(title, '{*\**}','order by price')>0; /* ???? */
-- Query for Special Characters mixed with other characters
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '*4*', 'order by price') > 0; /* finds %4% */
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '{44}', 'order by price') > 0; /* finds nothing */
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '{44*}', 'order by price') > 0; /* finds 44% */
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '{============== Cash ==================}','order by price') > 0;
SELECT title, price FROM AUCTION WHERE CATSEARCH(title, '{============== Cash ==================*}','order by price') > 0;
