Re: Index compression vs. table compression

From: Mark Bole <makbo_at_pacbell.net>
Date: Wed, 29 Dec 2004 19:30:13 GMT
Message-ID: <9BDAd.4488$yV1.3503@newssvr14.news.prodigy.com>

Howard J. Rogers wrote:
> Rick Denoire wrote:
>

>> Mark Bole <makbo_at_pacbell.net> wrote:
>>
>>
>>> The following terms appear: "data_segment_compression" for tables and 
>>> tablespaces, plus "key_compression" for indexes -- but there is not 
>>> an explanation why a tablespace attribute setting can cause table 
>>> partitions (and, presumably, non-partitioned tables) to default to a 
>>> given "compress" attribute, but not indexes.
>>
>>
>>
>> In other words: What happens when an index is rebuild to a compressed
>> tablespace? Don't know.

>
>
> Don't even go there!! You're talking about two different things. About
> as similar as two completely similar things in a pod. :-)
>

[...]
>
> There are all sorts of other "peculiarities" that might/will stump you
> if you persist in seeing the two forms of "compression" (actually,
> merely data 'suppression') as anything whatever to do with each other.
[...]
>
> Such things only seem odd if you expect tables to compress in the same
> sort of way indexes have been doing since 8.1.6. But doing so is a bit
> like expecting ASSM to be as benign as LMT, simply because both
> technologies use bitmaps to do their magic.
>
> Regards
> HJR
>
>

I for one am definitely in learning mode here, and you (HJR) have already cleared up several misconceptions I had. I'm digesting your two posts here as well as carefully reading Kyte's discussion of key compression and IOT's in the "Expert One-on-One" book.

If my understanding is correct, data segment compression employs the same underlying concept as other kinds of encoding-based compression (for example, "gzip" and "compress") -- from the Oracle documentation, "[data segment compression] is done by moving such repeated column values into a shared block-level symbol table and replacing occurrences with references into the symbol table".

As I came up with other questions about key compression defaults, I found most of them answered here:

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

Going with the OP's original claim that "there is a clear mismatch between the overwhelming computer power and the poor disks attached", it is easy to see the desire for a way to default all indexes in a given tablespace to COMPRESS, similar to actual ability to default all tables to the COMPRESS attribute (since he has CPU power to spare). It definitely appears to be a documentation bug that the description for CREATE TABLESPACE in the 9i version does not indicate at all what the meaning of the COMPRESS attribute is, specifically that it refers to data segment compression and not key compression. It's a little better in the 10g documentation, but not much.

-Mark Bole Received on Wed Dec 29 2004 - 13:30:13 CST