Databases Reference
In-Depth Information
Query the
INDEX_STATS
view to find the value of the
DEL_PCT
column, which shows the percentage of
the deleted leaf rows in the index.
SQL> select lf_rows,del_lf_rows,del_lf_rows/lf_rows*100 del_pct from index_stats;
LF_ROWS DEL_LF_ROWS DEL_PCT
-------- ----------- ----------
9 4 44.4444444
SQL>
As expected, the
DEL_PCT
column has a value of a little over 44%. Insert a single row into the table.
SQL> insert into reb_test values (999,1,'Franco Marx');
1 row created.
SQL> commit;
Commit complete.
SQL>
Analyze the index again.
SQL> analyze index reb_test_cust_id_idx validate structure;
Index analyzed.
SQL>
Check the percentage of the deleted rows now.
SQL> select lf_rows,del_lf_rows,del_lf_rows/lf_rows*100 del_pct from index_stats;
LF_ROWS DEL_LF_ROWS DEL_PCT
-------------- ---------------------- --------------
6 0 0
SQL>
As you can see, the deleted percentage of rows, which was about 44%, is now zero. The reason for
this is that while you've inserted a single row, that is still a quarter of the four rows initially deleted. If you
insert a small number of rows into a large table after deleting a high percentage of rows, don't expect to
see Oracle immediately reclaim the deleted space. The real point we're trying to make here is that the
Oracle database does in most cases utilize the space left free by deleted rows for inserting fresh rows; the
space doesn't necessarily end up as deadwood. If you're rebuilding indexes simply based on an arbitrary
cutoff point for the percentage of deleted space in an index, you may not see any real gains over time,
since the index itself may very well reuse all of the so-called wasted space. The final size of the index may
very well grow back to its "natural" state anyway. In addition, depending on the percentage of rows
currently marked as deleted based on the
DEL_IF_ROWS
statistic from the
INDEX_STATS
view means that
you may actually miss potential opportunities for a valid rebuild. This is so because under some
circumstances, the
DEL_IF_ROWS
statistic actually vastly underestimates the actual percentage of deleted
rows in an index.
