Hi,

Index fragmentation occurs when a row included in the index is deleted AKA index stagnation.

You will need to analyze you indexes individually to find those stagnated indexes, once discovered they can be rebuilt.

To analyze issue the following command :-

analyze index owner.index_name validate structure;

The index information will now be in table 'index_stats'

Now issue the following query :-

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
where name = 'index_ name'

If 20%+ of rows are deleted then the index should be rebuilt.

The index stats table can only hold one record of information at a time, therefore you will need to analyze each index individually and then interrogate index_stats, you can also automate this process using pl/sql.

Alternatively, you can use Oracle Enterprise Manager, Index Tuning Wizard.

This information was partly supplied by user : hacketta

Cheers

Suresh