Oracle Text indexes synchronisation

PURPOSE=======
To show how the Oracle Text indexes are synchronized and how to control it. 

SCOPE & APPLICATION===================
This note is for Oracle Text administrators and developers. 

Synchronizing Text Indexes
A text index always responds within the same transaction to DELETE in the indexed column, but the response to INSERT and UPDATE which is modeled as a DELETE followed by an INSERT is asynchronous. An internal trigger which isinvisible to the customer requests index maintenance by inserting a row intoa queue table in CTXSYS schema for each new row in the indexed column.This DML queue can be tracked by querying the CTX_PENDING or CTX_USER_PENDINGText data dictionary view. 
Example: 
  create table docs (id number primary key, doc varchar2(60));
  create index i_doc on docs (doc) indextype is ctxsys.context; 
  insert into docs values (1,'A man walking on the street with a barking dog.'); 
  select * from ctx_user_pending; 
  PND_INDEX_NAME                 PND_ROWID          PND_TIMES   ------------------------------ ------------------ ---------   I_DOC                          AAAHVoAAEAAAEiqAAA 14-NOV-06 
  select * from docs where contains(doc,'dog')>0; 
  no rows selected 
In this situation the table and the text index on a column of this table are notsynchronized. A text query on the indexed column does not return the new row eventhough the new document fulfills the condition. So the table and the index have to be synchronized. The above defined DML queue contains information about rows in the document tableto be synchronized with the text index. We say that the table is synchronized withits text index when there is no entry referencing to this text index in the DML queue. 
After synchronizing the table and its index text queries return with the new/updateddocuments fulfilling the condition. Of course, the entry referencing the rows to besynchronized disappear from the DML queue. 
Example: 
  select * from docs where contains(doc,'man')>0; 
         ID DOC   --------- ----------------------------------------------------------------           1 A man walking on the street with a barking dog 
  select * from ctx_user_pending; 
  no rows selected 
There are two approaches for synchronization: 
  1. Explicit one-shot request for a given index   2. Implicit continuous request for all indexes database-wide 
1. Explicit Synchronization 
Explicit synchronization can be implemented using ctx_ddl.sync_index PL/SQL procedure.
  begin   ctx_ddl.sync_index(idx_name => '<index_name>', memory => '50M');  end;
The memory parameter determines the maximum memory which will be allocated by theprocess which synchronizes the table and the index. Its default and maximum valueis determined by the DEFAULT_INDEX_MEMORY and MAX_INDEX_MEMORY system parameterrespectively which can be queried from CTX_PARAMETERS Text data dictionary viewand can be modified with ctx_adm.set_parameter PL/SQL procedure. It is worth setting larger values for faster completion and there will be fewer recordsper token in the token table. 

PL/SQL procedures have to be written for implementing synchronization in order to start jobs running automatically. Defining jobs requires at least one job queue process running. 
Example: 
  create or replace procedure my_index_sync   is   begin     ctx_ddl.sync_index( idx_name => 'my_index', memory => '50M');   end;   / 
  declare   v_job number;     begin    dbms_job.submit( job=>v_job, what=>'my_index_sync;', next_date=>sysdate, interval=>'sysdate+1/24');  end;   / 
One of the advantages of this method is to make differences between indexes,i.e. less important indexes can be syncronized rarely and more important often.So synchronization policy can be implemented for each text index. On the other hand, DBA always has to keep track the job system and if a new text index is created in the database DBA has to look after its synchronization. 

2. Explicit Synchronization for all text indexes database-wide 
This is a short script that runs sync in a loop with a variable delay.It remains logged on, continually scanning for DML to do on any index.
Note that this script will never exit on its own.

create table stop_sync(aa varchar(10)); declare  the_count integer;  sleep_time number := 5;begin  loop    -- scan and process pending dml to do    ctx_ddl.sync_index('null');    select count(*) into the_count from stop_sync;    exit when the_count > 0;    -- sleep for 5 seconds to avoid spinning    dbms_lock.sleep(sleep_time);  end loop;  delete from stop_sync;  commit;end;/
To stop it, you simply insert a row into the table "stop_sync" from another session.
insert into stop_sync values ('X');commit;