Rebuilding Index

[vijay8282]

Hi,

Whatis difference between

- Rebuilding index online

- Dropping index and then creating index again

Regds
Vijay

[marist89]

Rebuilding the index online keeps an index available while the rebuild is running. Drop/Create doesn't let your queries use an index while it is being created.

[jmodic]

Plus rebuilding is much quicker than drop+create - it usually involves substantialy less I/O (because the old index is usually much smaller than the table) and requires no sorting (as the indexed keys are allready sorted in the old index).

[vijay8282]

Hi,

Thanks,

Does it has to do something with the Space.

Means it frees space or something ?

Vijay

[Zaggy]

Deletes from a table are not removed from an index. So, yes it does free up space.

[tycho]

Hi,

It gives space back but first it builds a new index using the old index so you need space for both of them. When it finishes it drops the old one.

The best way to deal with rebuilding indexes is having a localy managed tablespace with a uniform extent size. This will help to get rid of fragmentation cause all extents will have the same size.

In practice I have seen indexes having only half their original size after the rebuild.

Tycho