We want to use a ctxsys.context
index type for full text search. But I was quite surprised, that an index of this type is not automatically updated. We have 3 million documents with about 10k updates/inserts/deletes per day.
What are your recommendations for syncing and optimizing an Oracle Text index?
I think 'SYNC EVERY' option, as described in previous answer only available in Oracle 10g or newer. If you're using older version of Oracle you would have to run sync operation periodically. For example, you can create following stored procedure:
and then schedule it run via DBMS_JOB:
As for index optimization, following command can be used (also can be scheduled with DBMS_JOB or via cron):
There is also CTX_* package with similar function available.