rebuild indexes oracle 10G

Question

When i need run process for rebuild indexes and how i can know what indexes i need to rebuild ?

Answer 1

In general, indexes and tables should be rebuilt when they become too fragmented.
In practice, you probably will not have need to do it with Oracle 10g. Fragmentation occurs on tables and indexes with lots of changes to structure (adding/removing columns) and lots of data changes (insert, update, delete).

From v10, Oracle have number of automated processes that take care about database performance. One of them is "Segment advisor" that runs automatically.

If this is full version of Oracle 10g (not express), then you have database manager dashboard.
There you will find "Advisor central" where you can find maintenance information for each part of Oracle.
Among other, there is "Segment advisor" report where you can find recommendations for performance improvement. There you can select recommended actions and tell Oracle to do it.

If you are suspicious about performance on some table or index, you can execute analysis on demand and you will get recommendation if table or indexes should be rebuilt.

You can check it daily if you have large databases with lots of users and lots of changes.

Answer 2

Regular rebuilding of indexes is a religion that many adhere to but which few can justify. Almost every case made for it is faulty, generally because it has unintended side-effects such as increasing the subsequent system load due to block splits as the index returns to it's natural state of sparsity.

Before even thinking about rebuilding indexes you should first research how indexes work, how entries are made and removed, and the effect on performance of having compact or sparse indexes -- it is not hard to find this information through Google.

Also consider a coalesce operation instead of a rebuild.

Answer 3

Read here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112 about rebuilding indexes. Rebuilding bitmap indexes is ok, but btree indexes not so.