How to maintain full-text search performance

Each time an OracleText full-text index is synchronized, the index becomes more fragmented. The higher the index fragmentation, the worse the search performance becomes. Index fragmentation is unavoidable in a live database where records are actively created, updated and deleted. The rate at which the index fragmentation increases depends on the index synchronization setting and the rate at which records are created, updated or deleted. 

Monitoring the index fragmentation

To obtain statistics about the full-text index execute a script similar to the following:

create table index_stats (ts timestamp, report clob);

declare 
  x clob := null;
begin
  ctx_report.index_stats('aq_fulltext_index', x);
  insert into index_stats (report) values (x);
  dbms_lob.freetemporary(x);
end;

select * from index_stats;

It is recommended to monitor the index fragmentation and determine the maximum index fragmentation where search performance is still acceptable. Based on this, an index maintenance plan can be implemented that will periodically optimize or rebuild the index. A decision must also be made on the tradeoff between index availability and index fragmentation:

• The less frequently an index is synchronized, the more it takes for changes to aggregates and documents to be available through full-text search.
• The more frequently an index is synchronized, the faster the index fragments and performance degrades.

Optimizing the full-text index

A fragmented index can be optimized in two ways:

• By using the CTX_DDL.OPTIMIZE_INDEX procedure.
• By rebuilding the index.

To optimize the index using a the CTX_DDL.OPTIMIZE_INDEX procedure, execute one of the following statements:

-- for heavily fragmented indexes
CALL ctx_ddl.optimize_index('aq_fulltext_index', 'REBUILD')
 
-- for a frequently used token
CALL ctx_ddl.optimize_index('aq_fulltext_index', 'TOKEN', TOKEN=>'example');

Please consult the reference documentation for CTX_DDL.OPTIMIZE_INDEX for details on the various other options available for this stored procedure.

To optimize the index by rebuilding it, the following statement may be executed:

ALTER INDEX aq_fulltext_index REBUILD;

Please consult the reference documentation for OracleText ALTER INDEX REBUILD syntax for various other available options when rebuilding the index.