Maintaining Text Index

About Text Index Maintenance

The text index is expensive in terms of creation and maintenance, especially if a database is large, or dynamic in nature. Index maintenance is necessary after you perform DML (INSERT, UPDATE, or DELETE) on the base table.

The Text Indexes can be maintained using the option Sync/optimize Text Index in CES. This feature is used to schedule or remove or change the job to Synchronize or Optimize Text Index. The topics covered in this section are:

Synchronizing Text Index

The Sync/Optimize Text Index allows scheduling job to synchronize the text index with the base table at regular intervals or once.

This task explains how to synchronize the text index:

  1. From the navigation pad, select Tools >Text Management > Sync/Optimize Text Index. The Sync/Optimize Text Index page appears.

Oracle Text does not synchronize updates or additions to the text index with base table updates and additions. Generally, changes to the text index are done at a later stage in batches, and separately from the base table changes.

Optimizing Text Index

The Sync/Optimize Text Index allows scheduling job to optimize the text index at regular intervals or once.

This task explains how to optimize the text index:
  1. From the navigation pad, select Tools >Text Management > Text Index. The Text Index page appears.

  2. In the Sync/Optimize Text Index page, click the Optimize tab. The Sync/Optimize Text Index - Optimize page appears.

When additions are made to the text indexes, the indexes become fragmented. When changes are made to text index, the garbage collection needs to be done. These two issues can be solved by optimizing the text index. We recommend you to delete the statistics on the CISDBA.DR$tables before you optimize the text index.

The optimization has two options FAST and FULL. The default optimization mode is FAST.

In FAST optimizing mode, Oracle compacts the fragmented rows. But it does not remove the old data (garbage collection).

In FULL mode, the entire index is optimized and old data is removed. As the FULL mode does the garbage collection, it might take a long time for optimization. In such cases, the FULL mode with specifying Maximum Optimization Time option might be used.

When you specify Maximum Optimization Time, the optimization of the text index runs for the specified time and stops. The next time when you run optimization, it will pick up from where it left off. This feature helps to do the optimization during OFF peak hours every day.

Scheduling, Changing, or Removing Job to Sync/Optimize Text Index

Schedule, Change, or Remove job to Sync/Optimize Text Index These options requires the init.ora parameter JOB_QUEUE_PROCESSES to be set to non-zero value greater than one.

This task explains how to schedule, change or remove job to Sync/Optimize Text Index.
  1. In the navigation pad, select Tools>Text Management>Sync/Optimize Text Index.  The following table details the options:
     
    Options Details
    Schedule Select an index from the list and click Schedule to schedule a job to sync/ Optimize the text index. You are prompted to provide the frequency of the job. The default frequency is set to every day at 12:00 A.M. You can change the default to run the sync every nth interval or run once.
    Change Click Change to change the scheduled/Optimized jobs.
    Remove Click Remove to remove the job.
    Reload Info. Click Reload Info. Info to reload the job details.