index rebuild vs compress

zeb

unread,

Dec 21, 2003, 3:48:07 AM12/21/03

to

Hi,

What is the difference between index rebuild and compress ?
which is better ?

Thanks in advance
( Oracle8.1.7 , HP-UX 11.0 )

Ana C. Dent

unread,

Dec 21, 2003, 9:38:48 AM12/21/03

to

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?

Jim Kennedy

unread,

Dec 21, 2003, 12:52:09 PM12/21/03

to

"Ana C. Dent" <anac...@hotmail.com> wrote in message
news:ZfiFb.35999$BQ5.3895@fed1read03...

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

Geomancer

unread,

Dec 21, 2003, 3:03:46 PM12/21/03

to

"zeb" <thierry.cons...@free.fr> wrote in message news:<3fe55dbc$0$24019$626a...@news.free.fr>...

> Hi,
>
> What is the difference between index rebuild and compress ?
> which is better ?

Rebuild does only re-balance inodes while compress makes index less
I/O intense, influencing the CBO:

http://www.jlcomp.demon.co.uk/faq/compress_ind.html

http://www.jlcomp.demon.co.uk/ch_07.html

Mark D Powell

unread,

Dec 21, 2003, 3:06:29 PM12/21/03

to

"Ana C. Dent" <anac...@hotmail.com> wrote in message news:<ZfiFb.35999$BQ5.3895@fed1read03>...

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