Text Index Synchronization and Optimization

Oracle Text is a core component of iFS, Ultra Search, and XML DB. The indexing and searching abilities of Oracle Text are full-text retrieval against virtually any datatype (including all LOB types). They are not restricted to data stored in the database. It can index and search documents stored on the file system and index more than 150 document types, including a search on widget and find documents that contain the work gadget.

How to apply Oracle Text and Text Index can be found here:
http://www.oracle.com/technology/oramag/oracle/04-sep/o54text.html

The Oracle Text indexes must be periodically synchronized so that new data is included in the indexes. This process can be scheduled to run on a periodic basis for Oracle 9i. The Procedure 'CTX_DDL.SYNC_INDEX' is used to synchronize text index. Oracle 10g supports an improved method of index synchronization over Oracle 9i. Oracle 10g has the ability to synchronize the Text indexes as data is saved. This method does not require background jobs to be configured.

In systems with frequent record modification or creation, the Text indexes will become fragmented over time. This fragmentation will lead to slow performance during index synchronization, saving data, and searching. The Procedure 'CTX_DDL.OPTIMIZE_INDEX' should be used to optimize the Text indexes periodically.

Text index types: CONTEXT, CTXCAT, or CTXRULE.

To make sure which indexes are Text indexes, log into user account and issue the following command:
SELECT idx_name
FROM ctxsys.ctx_indexes
WHERE idx_owner='';

Text Packages and associated information can be found in the Oracle official document 'Oracle Text Reference'.