Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.