Re: index rebuild vs compress

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<ZfiFb.35999$BQ5.3895_at_fed1read03>...
> zeb wrote:
> > Hi,
> >
> > What is the difference between index rebuild and compress ?
> none
> > which is better ?
>
> neither
>
> P.S.
> EXACTLY how does one "compress" an index?

To answer how one compresses an index, why by using the create index parameter "compress" which "eliminates repeated occurrence of key column values and may substantially reduce storage." (SQL Manual ver 9.2) The compress option can be applied during an alter index rebuild; however, I wonder if Zeb really meant to ask the difference between coalesce and compress in regards to an index rebuild.

Coalesce merges index blocks where possible to free blocks that are added back to the freelist. It works within the existing space allocation to try to effectively 'compress' the existing index information into less data blocks.

Compress on the other hand works on a create or index rebuild to reduce storage used to hold the leading columns in a multicolumn key.

The index rebuild process reads the index, sorts the information, and inserts it into a new index structure working from the beginning of the space allocation toward the rear where all the free blocks would be immediately after the rebuild.

HTH -- Mark D Powell -- Received on Sun Dec 21 2003 - 14:06:29 CST