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?
>

alter index xxx compress; (which coes a rebuild of an index)

or when building it initially

create index xxx on myTable(col1,col2) compress ...

It compresses repeated values in the index and makes it smaller.

For example, if you have a table like: (assuming in the US)

create table zipCodes (

State  varchar2(50) not null,
City   varchar2(50) not null,
zipcode numbert(5,0) not null);

You could look up the zip code with by specifying State and City and thus you might put an index on State and City. create index zip_index on zipCodes(State,City) compress; So Oracle will make a much smaller index than without the compress. (Compressing the repeated State and City values.) The size difference can be dramatic. More of the index can be cached. Memory is used a little more efficiently.

If you are going to do some large data load then a compressed index is going to take more resources to update than a non compressed index. In a normal oltp or where you are reading a lot more than writing you won't notice the difference.

Jim Received on Sun Dec 21 2003 - 11:52:09 CST