Index Rebuild

[Thomas7]

Hi Friends,

I have quick question. When and why should I rebuild my indexes. Thanks in advance for your help.

[ebrian]

Check the authority Oracle Index Internals - Rebuilding The Truth.

[gandolf989]

Check the authority Oracle Index Internals - Rebuilding The Truth.

Amazing, it takes 166 pages to say that based on Oracle using a self balancing B-Tree index, indexes should not need to be rebuilt. Here is something that explains the storage structure for a btree index. http://www.virtualmachinery.com/btreeguide.htm or
http://www.bluerwhite.org/btree/

Back when Oracle 7.3.4 was the standard, and if a database was using 4k or 2k block sizes, it was possible for indexes to reach the maximum number of extents. Under that situation you would need to rebuild an index to specify different storage parameters. However, with the advent of Oracle 8i, and locally managed tables, this is not longer a concern. So IMHO, if there was ever a valid reason to build indexes, it was probably more about managing extents than anything else.

[giteshtrivedi]

Hi,

Only hot indexes or volatile indexes are needed to be rebuild.Otherwise not needed to rebuild regular.

Thanks and regards,
Gitesh
http://www.dbametrix.com

[davey23uk]

Hi,

Only hot indexes or volatile indexes are needed to be rebuild.Otherwise not needed to rebuild regular.

Thanks and regards,
Gitesh

what utter rubbish, its statement like this that keep this stupid argument going on forever

[ebrian]

Amazing, it takes 166 pages to say that based on Oracle using a self balancing B-Tree index, indexes should not need to be rebuilt.

And yet the pages you posted made no mention of rebuilding an index

Since the OP's question was in regards to rebuilding indexes and not the low-level mathematic algorithms or science behind indexes, careful reading of the doc does mention that rebuilds are beneficial under certain circumstances.

Nevertheless, even if the doc was 10066 pages, sometimes it is still impossible to dispel the myths that are inevitably perpetuated as evidenced by Gitesh's comment above.

[gandolf989]

And yet the pages you posted made no mention of rebuilding an index

Since the OP's question was in regards to rebuilding indexes and not the low-level mathematic algorithms or science behind indexes, careful reading of the doc does mention that rebuilds are beneficial under certain circumstances.

Nevertheless, even if the doc was 10066 pages, sometimes it is still impossible to dispel the myths that are inevitably perpetuated as evidenced by Gitesh's comment above.

If you understand what a basic binary search tree is, and how performance can improve with the use of self balancing trees, you might realize that with a btree index, the index will end up in the most efficient state. Instead of thinking about indexes as a black box where you have no idea about the implementation, having a full understanding of the inner workings of the index will help dispel the index rebuilding myths that have perpetuated so much over the years. So yes, look at the underlying data structure.