Oracle Text como comenzar

e://

- Como comenzar a usar Oracle text?
- Que es lo primero que debo saber?
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
- How to start using Oracle text?
- What is the first thing I should know?

Barbara Boehmer

If you want to search for special characters, then you have to do both. You have to declare special characters as printjoins, so that they are tokenized and indexed and you have to enclose the special characters in {} to search for them, so that Oracle treats them as characters to search for instead of their special meanings.

Herald ten Dam

Hi,

start reading the manuals. There are 2:
1: Text Application Developer's Guide, see http://www.oracle.com/pls/db102/to_toc?pathname=text.102%2Fb14217%2Ftoc.htm&remark=portal+%28Books%29
2: Text Reference, see http://www.oracle.com/pls/db102/to_toc?pathname=text.102%2Fb14218%2Ftoc.htm&remark=portal+%28Books%29

I gave you the 10.2 manuals, but they also exist for 11.1 and 11.2. But the basics for text are the same for all versions.

Herald ten Dam
Superconsult.nl

e://

I index my table_X :

CREATE INDEX ON table_X (column_X) INDEXTYPE IS CTXSYS.CONTEXT;

and then
run the search query:

SELECT column_X
FROM table_x
where CONTAINS( column, 'ZZZ') >= 0;


This is enough?

Herald ten Dam

Hi,

the syntax is correct if you use contains(column_x,'ZZZ') > 0 and give the index a name.

You have to look at the synch mechanisme for a context index. Normally if you insert in the table table_X, Oracle will not synchronize the index on commit. You can make this work in 2 manners:
1. You make a job and call for example every hour DBCTX_DDL.Sync_Index.
2. You include in your index a Synch clause:

CREATE INDEX TEST_TXT_INDEX ON table_X (column_X) INDEXTYPE IS CTXSYS.CONTEXT parameters ('sync (on commit)');

This example will synch the index on a commit.

Herald ten Dam
Superconsult.nl

e://

then I need to consider synchronizing my table so that the new content is indexed..
Truth?

Herald ten Dam

Hi,

you have to consider the synchronization of the index. If you add a record to the table, when is it necessary to see the newly added data in the index. Do you need it when it is committed (directly) or can you wait to see it, for example an hour later? Synchronizing the index will put the data of the column from the table in the index.

The penalty for "synch on commit" is that a insert/update on the table has to wait for the index to synchronize.

Herald ten Dam
Superconsult.

e://

Hi,

I understand!

3 Tips important
. Index table with (CTXCAT - CONTEXT etc...)
. Synchronize on the Insert / Update
. And run the query

What type of indexing should I use for blob columns ??
Some examples?

Herald ten Dam

Hi,

for blob columns use the CONTEXT, CTXCAT is more used for short text and for mixed indexing, the use of a text field and a other normal database field.
For examples see the hereabove mentioned documentation. All your questions are also answered in these manuals. It is definitily the best start to learn TEXT. Further you have on Technet some examples, see http://www.oracle.com/technology/sample_code/products/text/index.html. The first item gives also an introduction to TEXT.

Success

Herald ten Dam
Superconsult.nl

e://

Hi !!

Indexing a blob field I, now search the contents of those documents.

I have to look for 2 columns:

-------Title--------------------------------Doc
The dog is wonderful---------the_dog1.pdf
The dog is dog------------------the_dog2.pdf
The dog---------------------------the_dog3.pdf
The cat-----------------------------the_cat.pdf

Querys:

SEARCH TITLE:

select TITLE, SCORE(1) s
from IDX_BOOKS
where contains (TITLE, :P9999_SEARCH,1) > 0
order by s desc;


SEARCH DOC (blob):

select TITLE, SCORE(1) s
from IDX_BOOKS
where contains (DOC, :P9999_SEARCH,1) > 0
order by s desc;


as mix 2 query in 1 query??

I have to use CATSEARCH?

Barbara Boehmer

You can use a multi_column_datastore, as demonstrated below.

[email protected]_11g> CREATE TABLE idx_books
  2    (title	      VARCHAR2 (30),
  3  	doc	      BLOB,
  4  	title_or_doc  VARCHAR2 (1))
  5  /

Table created.

[email protected]_11g> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle11g'
  2  /

Directory created.

[email protected]_11g> DECLARE
  2    v_blob	BLOB;
  3    v_bfile	BFILE;
  4  BEGIN
  5    INSERT INTO idx_books (title, doc)
  6  	 VALUES ('The dog is wonderful', EMPTY_BLOB())
  7  	 RETURNING doc INTO v_blob;
  8    v_bfile := BFILENAME ('MY_DIR', 'the_dog.pdf');
  9    DBMS_LOB.OPEN (v_bfile, DBMS_LOB.LOB_READONLY);
 10    DBMS_LOB.LOADFROMFILE
 11  	 (v_blob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
 12    DBMS_LOB.CLOSE (v_bfile);
 13    --
 14    INSERT INTO idx_books (title, doc)
 15  	 VALUES ('The cat', EMPTY_BLOB())
 16  	 RETURNING doc INTO v_blob;
 17    v_bfile := BFILENAME ('MY_DIR', 'the_cat.pdf');
 18    DBMS_LOB.OPEN (v_bfile, DBMS_LOB.LOB_READONLY);
 19    DBMS_LOB.LOADFROMFILE
 20  	 (v_blob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
 21    DBMS_LOB.CLOSE (v_bfile);
 22  END;
 23  /

PL/SQL procedure successfully completed.

[email protected]_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE
  3  	 ('your_datastore', 'MULTI_COLUMN_DATASTORE');
  4    CTX_DDL.SET_ATTRIBUTE
  5  	 ('your_datastore', 'COLUMNS', 'TITLE, DOC');
  6  END;
  7  /

PL/SQL procedure successfully completed.

[email protected]_11g> CREATE INDEX book_index
  2  ON idx_books (title_or_doc)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('DATASTORE your_datastore')
  5  /

Index created.

[email protected]_11g> VARIABLE p9999_search VARCHAR2(30)
[email protected]_11g> EXEC :p9999_search := 'dog'

PL/SQL procedure successfully completed.

[email protected]_11g> select TITLE, SCORE(1) s
  2  from   IDX_BOOKS
  3  where  contains (title_or_doc, :P9999_SEARCH, 1) > 0
  4  order  by s desc
  5  /

TITLE                                   S
------------------------------ ----------
The dog is wonderful                    4

[email protected]_11g> 

e://

what the use is title_or_doc column ??