Why Text Search Works for Old Indexed Data but Not for all New Added Data

APPLIES TO:

Oracle Text - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.
GOAL

How to implement Text index maintenance strategy for non-transactional context indexes?

SOLUTION

When documents in the base table are inserted, updated, or deleted, their ROWIDs are held in a DML queue until you synchronize the index. You can view this queue with the CTX_USER_PENDING view.

For example, to view pending DML on all your indexes, issue the following statement as index owner:

SELECT pnd_index_name, pnd_partition_name, pnd_rowid, to_char(pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp FROM ctx_user_pending;

1. Synchronizing the index involves processing all pending updates, inserts, and deletes to the base table. You can do this in PL/SQL with the CTX_DDL.SYNC_INDEX procedure.

The following example synchronizes the index with 50 megabytes of memory:

begin
 ctx_ddl.sync_index('<index_name>', '50M');
end;
/

2. Or use the automatic sync feature and specify that the index be synchronized at regular intervals. To do so, create the index with the SYNC (EVERY "interval-string") parameter.

To use job scheduling, you must log in as a user who has DBA privileges and then grant CREATE JOB privileges.

CONNECT system/passwd@tns_alias
GRANT CREATE JOB TO <index_owner>;

CONNECT <index_owner>/passwd@tns_alias
alter index <index_name> rebuild parameters ('replace metadata sync (every "SYSDATE+5/1440")');


Or create a job through Oracle Scheduler (the Scheduler) that synchronizes the text index every 5 minutes.
Example:

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
     job_name => 'sync_myindex1',
     job_type => 'PLSQL_BLOCK',
     job_action => 'ctx_output.start_log(''sync_myindex1.log''); ctx_ddl.sync_index(''MYINDEX1'', ''50M''); ctx_output.end_log();',
     start_date => SYSDATE,
     repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;
     comments => 'syncing index MYINDEX1');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME                      ENABLED
----------------------------- -------
SYNC_MYINDEX1                 FALSE


BEGIN
 DBMS_SCHEDULER.ENABLE ('sync_myindex1');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME                      ENABLED
----------------------------- -------
SYNC_MYINDEX1                 TRUE

SELECT JOB_NAME, STATUS FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'SYNC_MYINDEX1';

JOB_NAME                                STATUS
--------------------------------------- ------------------------------
SYNC_MYINDEX1                           SUCCEEDED


Frequent index synchronization can fragment your CONTEXT index. Index fragmentation can adversely affect query response time. You can optimize your CONTEXT index to reduce fragmentation and index size and so improve query performance.

With the CTX_REPORT.INDEX_STATS procedure, you can create a statistical report on your index. The report includes information on optimal row fragmentation, list of most fragmented tokens, and the amount of garbage data in your index. Although this report might take long to run for large indexes, it can help you decide whether to optimize your index.

To optimize an index, Oracle recommends that you create a job through the Scheduler and run CTX_DDL.OPTIMIZE_INDEX.

Example:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name          =>  'opt_rebuild_myindex1',
   job_type          =>  'PLSQL_BLOCK',
   job_action        =>  'ctx_output.start_log(''opt_myindex1.log''); ctx_ddl.optimize_index(''MYINDEX1'',''REBUILD''); ctx_output.end_log();',
   start_date        =>  SYSDATE,
   repeat_interval   =>  'FREQ=WEEKLY; BYDAY=SUN;', /* every sunday */
   comments             => 'optimize index MYINDEX1 in rebuild mode');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME                       ENABLED
------------------------------ -------
OPT_REBUILD_MYINDEX1           FALSE


BEGIN
 DBMS_SCHEDULER.ENABLE ('opt_rebuild_myindex1');
END;
/

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME                       ENABLED
------------------------------ -------
OPT_REBUILD_MYINDEX1           TRUE

SELECT JOB_NAME, STATUS FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'OPT_REBUILD_MYINDEX1';

JOB_NAME                       STATUS
------------------------------ --------------------
OPT_REBUILD_MYINDEX1           SUCCEEDED