Oracle auto updating
While it is tempting to write a script that rebuilds every index in the schema, bear in mind that Oracle contains many thousands of indexes, and a complete rebuild can be very time consuming.
These workloads have these characteristics: The rules for identification of candidates for index coalescing/rebuilding depend on your specific index state.For now, the most any Oracle professional can do is to explore their indexes and learn how the software manages b-tree structures.When can we "prove" a benefit from an index rebuild?The DELETE with the multiple NOT EXISTS went from running for 2 hours to delete 30,000 records, to deleting over 100,000 records in minutes. ." An Oracle ACE notes this script to rebuild his indexes.
"I eventually wrote a simple query that generates a list of candidates for index rebuilds, and the commands necessary to rebuild the indexes once the tables reached a point where it was necessary.For example, indexes that are always accessed vis an index unique scan" will never need rebuilding, because the "dead space" does not interfere with the index access.