How can I tell when my indexes are fragmented?

Answer: The best way is to time some queries, run index optimization, and then time the same queries (restarting the database to clear the SGA each time, of course). If the queries speed up significantly, then optimization was worthwhile. If they do not, then you can wait longer next time.

You can also use CTX_REPORT.INDEX_STATS to analyze index fragmentation.

See Also:

• Oracle Text Reference to learn more about using the CTX_REPORT package

• “Optimizing the Index”