Index compression vs. table compression

From: Rick Denoire <100.17706_at_germanynet.de>
Date: Tue, 28 Dec 2004 22:22:45 +0100
Message-ID: <4hi3t0l7efahpe916hmmf1a82clacllvs9@4ax.com>

Hello

I run Oracle 9.2.0.5 on a RedHat AS 2.1 box with 8 Xeon CPUs. There is a clear mismatch between the overwhelming computer power and the poor disks attached. I am contemplating to setup a tablespace with compression and a blocksize of 16K (queries are of Data Warehouse type), and the corresponding 16k db cache (there is enough RAM), in order to take better advantage of this situation (the standard blocksize is 8K).

That is quite simple to do. A simple "alter table xxx move compress tablespace DWH_16C" should do it. But with indexes, there is a difference I don't quite understand. Doing "ALTER INDEX xxx REBUILD COMPRESS y TABLESPACE DWH_16_IDX" would work, but I don't understand the meaning of "y" in this case.

It seems that index compression requires a special attention for every single index to be compressed.

Could someone illuminate me in this matter?

Thanks
Rick Denoire Received on Tue Dec 28 2004 - 15:22:45 CST