Automatic SYNC in 10g


For CONTEXT indexes, when the CTXSRV process was desupported back in 8.1.6, we were all supposed to use CTX_DDL.SYNC_INDEX. This is still the case, but at least now, you can bundle it all up in a single DDL

create index TEXT_IDX on T( TEXT_COL )
indextype is ctxsys.context
parameters ('sync (every "SYSDATE+10/1440")');

which creates DBMS_SCHEDULER job for you, in this ase, running every 10 minutes. Another alternative is the "sync-on-commit" which ensures your text indexes stay right up to date (at the cost of index fragmentation and of course, commit performance)

create index TEXT_IDX on T( TEXT_COL )
indextype is ctxsys.context
parameters ('sync (on commit)');