Index Rebuild

Printable View

10-16-2009, 04:49 PM
Thomas7

Index Rebuild

Hi Friends,

I have quick question. When and why should I rebuild my indexes. Thanks in advance for your help.
10-16-2009, 11:39 PM
ebrian

Check the authority Oracle Index Internals - Rebuilding The Truth.
10-19-2009, 08:08 PM
gandolf989

Quote:

Originally Posted by ebrian

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. :rolleyes: 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.
10-20-2009, 12:00 AM
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
10-20-2009, 03:33 AM
davey23uk

Quote:

Originally Posted by giteshtrivedi

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
10-20-2009, 07:23 AM
ebrian

Quote:

Originally Posted by gandolf989

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 :rolleyes:

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.
10-20-2009, 09:03 AM
gandolf989

Quote:

Originally Posted by ebrian

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

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.