How to sync and optimize an Oracle Text index?

Question

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?

Answer 1

Putting this here as an update for Oracle 12C users. If you use the index in real time mode, then it keeps items in memory, and periodicially pushes to the main tables, which keeps fragmentation down and enables NRT search on streaming content. Here's how to set it up

exec ctx_ddl.drop_preference ( 'your_tablespace' );
exec ctx_ddl.create_preference( 'your_tablespace', 'BASIC_STORAGE' );
exec ctx_ddl.set_attribute ( 'your_tablespace', 'STAGE_ITAB', 'true' );
create index  some_text_idx on your_table(text_col)  indextype is ctxsys.context PARAMETERS ('storage your_tablespace sync (on commit)')

this will set up the index in NRT mode. It's pretty sweet.