My Oracle Support Banner

Explanation of Index Fragmentation and Optimization (Rebuilld) when using OracleTextSearch or DATABASE.FULLTEXT (Doc ID 1087777.1)

Last updated on MARCH 05, 2024

Applies to:

Oracle WebCenter Content - Version 10.0 and later
Information in this document applies to any platform.

Purpose

WebCenter Content (WCC) performs a "FAST" optimization of the full text index automatically after every 5000 indexed documents. 

In addition, regular "FULL" and "REBUILD" optimization of the text indexes used with WCC is also needed to avoid performance issues.

The best management of the index is to schedule a job on the database to run the optimization on a regular basis.  Prior to switching the optimize indexes on DB scheduler disable the OPTIMIZE process in WCC:

1. In the config.cfg file set the following parameters:

RebuildOptimizationInterval=50000000
FastOptimizationInterval=50000000

2. Restart the WCC managed server(s)

3. On the WCC go to Administration --> Scheduled Jobs Administration --> Active Scheduled Jobs

4. If Opt_FT_IdcText1 or Opt_FT_IdcText2 are listed

In the Action drop down list, select Delete

Steps for scheduling a DB job follow at the end of this note. First, some information on checking for fragmentation in the WCC indexes.When using full text search in WCC, with either of the following settings;

the database uses Oracle Text index(es) to manage the data for searching.  When using Oracle Text indexes, there is a need to maintain the index on the database in order to keep the disk from getting fragmented.

To avoid fragmentation, an optimize_index procedure should be run nightly to de-fragment the index.  This should be scheduled by the DBA

To determine what is the active index on a WCC system

Navigate to WWC (as admin) -> Administration -> Configuration

example for:  OracleTextSearch:
Search Engine::ORACLETEXTSEARCH
Index Engine Name:ORACLETEXTSEARCH
Active Index:ots2

example for DATABASE.FULLTEXT:
Search Engine::DATABASE.FULLTEXT
Index Engine Name:DATABASE.FULLTEXT
Active Index:IdcColl1

The "active index" values on this page show the table and index is active in the database.

SearchIndexerEngineNameActive indexDatabase tableDatabase index
OracleTextSearch ots1 IDCTEXT1 FT_IDCTEXT1
OracleTextSearch ots2 IDCTEXT2 FT_IDCTEXT2
DATABASE.FULLTEXT IdcColl1 IDCCOLL1 FT_IDCCOLL1
DATABASE.FULLTEXT IdcColl2 IDCCOLL2 FT_IDCCOLL2

To determine if an Oracle Text index is fragmented and possibly in need of optimization, a report called INDEX_STATS from the CTX_REPORT package can provide a useful view into the WCC indexes used for OracleTextSearch.  For information on the CTX_REPORT package, refer to the Oracle Text Reference guide.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Purpose
Troubleshooting Steps
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.