We have couple of tables with over 850 Million records. Production team is rebuilding these indexes few times every year. This is a OLTP application. We do not have any deletes on these tables. Most of the DMLs on these tables are only inserts/updates. Updates on these tables may be causing some DEL_LF_ROWS. These tables are not partitioned. We do not have any bulk operations on these tables
If the indexes are using lots of space for DEL_LF_ROWS, then we may need to rebuild indexes to move the indexes to the other tablespaces to release space to the OS.
I would like to know if there are any other reasons for rebuilding indexes.
I appreciate any inputs on this.
Thanks Vijay