At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?

Question

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.

I guess that it depends on the amount of indexed data, the time after which the index does not "performs well" (maybe after a lot of writes), the time taken by running CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) (is it "blocking" DB when running?), etc. so the answer to this question may not be very precise.

However, I would like to know if there are some general rules, principles, recommendations to follow (couldn't find any).

Answer 1

You can try to use the index statistics report by first enabling reporting on the index (BEGIN ctx_output.enable_query_stats(ix_my_indexname); END;). Then you can use the procedure ctx_report.index_stats. While sifting through the CLOB of info in the report, the most useful information to me is the FRAGMENTATION STATISTICS...estimated row fragmentation: 23%

see Oracle documentation here for more thorough examples of the stats report.