When should one rebuild an index? (for DBA)

Question

Helloo
When should one rebuild an index? (for DBA)
please reply me. i am waiting for your reply.
Thank you.

Answer 1

You can run the 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.