Oracle Text Indexing

When oracle rebuilds an text index (CTXCAT,CONTEXT or CTXRULE) it does a full scan to the data, so it should be rebuild only in a certain period (like once a day or twice a day, depending on the application).

You can build/rebuild the index by executing:
SQL> EXEC CTX_DDL.SYNC_INDEX('text_column_idx');
SQL> EXEC CTX_DDL.OPTIMIZE_INDEX('text_column_idx','FULL');

Index optimization can be performed in three modes (FAST, FULL or TOKEN). Êxample used FULL.

You should use this kind of indexes if your information is more or less static (at least you can live with no refreshes to the information during a certain period).

Oracle Text

Oracle Text works with regular data columns, blob and doc, pdf files stored.
There are different index types for oracle text:

CTXCAT Indexes

A CTXCAT index is best for smaller text fragments that must be indexed along with other standard relational data (VARCHAR2).

example:
WHERE CATSEARCH(text_column, 'ipod')> 0;


CONTEXT Indexes

The CONTEXT index type is used to index large amounts of text such as Word, PDF, XML, HTML or plain text documents.

ex: WHERE CONTAINS(test_column, 'ipod', 1) > 0

CTXRULE Indexes
A CTXRULE index can be used to build document classification applications.