When to Rebuild Index

[afshin]

Hi

Can Anybody please tell when should one rebuild an Index and how often ...

I would appreciate a document on that or some good material dba support help is perhaps the best so that will be perfect ..

Thanks .

Afshin

[tomate]

Hi there,

If your direct-load INSERT modifies most of the data in a table, you can avoid the performance impact of index maintenance by dropping the index before the INSERT and then rebuilding it afterwards.

Regards
Thomas

[afshin]

Hi

Thanks for your prompt reply ..
Thats now what I want . I want How does index fragmentation occur and how should I detect that the index is getting in efficient and how do I rebuild the same ........


Thanks

Afshin

[Sureshy]

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

[afshin]

Thankx Suresh

Appreciate it ..

[nabaig]

When I do a export import, does it have any positive./negative effect on my indexes?

Nizar

[jmodic]

Yes, it does have positive effects. After importing the table, its indexes are freshly rebuilt. So in fact it has the same consequences as ALTER INDEX REBUILD, you are just achieving it the hard way .