This content has been marked as final. Show 2 replies
Do you optimize the index at all?
How frequently is the table updated?
With "sync(on commit)" your index will get more and more fragmented as each new record is sync'd.
You need to run ctx_ddl.optimize_index on a regular basis to remove this fragmentation. If you do that, you shouldn't need to recreate the index regularly.
A typical optimzation regime is to run it in "FULL" mode nightly, and then run "REBUILD" mode once a week, or once a month.