Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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).

share|improve this question

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.

share|improve this answer
Thanks for those very interesting "starting points". – Frosty Z Mar 21 '12 at 18:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.