When you add rows to a table, each index on that table will also grow by the number of records added. However, when you delete rows from a table, the records are not physically, but logically deleted from the index. This will lead to index growth even if the size of the table remains fairly static.

For this reason it's good housekeeping practice to drop and rebuild indexes every so often. There is a straightforward way of to determine which indexes have large numbers of deleted rows. Firstly, use the validate index command to gather information about an index. In order to demonstrate this, in the illustration below we have created a table as a copy of dba_tables. This table has been this table istuff. Then we have created an index on the owner column called istx. Following this, we have deleted a large number of rows from the table and then checked on the index status:

validate index istx

This populates index_stats which we can now select from, note that this table only holds one row. Desc it to see what it holds; what we're interested in is the number of rows and the number of deleted rows so:

SQL>
1 select name, lf_rows, del_lf_rows, (del_lf_rows * 100) / lf_rows
2* from index_stats
SQL> /

NAME LF_ROWS DEL_LF_ROWS (DEL_LF_ROWS*100)/LF_ROWS
------------------------------ ---------- ----------- -------------------------
ISTX 373 286 76.6756032

76.67% of this index is logically deleted. Since a lot of space is being wasted this index should be dropped and rebuilt.