Re: Index compression vs. table compression

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 30 Dec 2004 10:16:26 +1100
Message-ID: <41d33acb$0$16979$afc38c87@news.optusnet.com.au>

Mark Bole wrote:
> 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

Ah. I think I see your point now. If a tablespace is set to COMPRESS by default, you expect to be able to create an index in there and have it automatically compress(1)??

If so, I agree that it could be confusing. It's certainly not evident from the words alone that the attribute only relates to table block compression.

But then, Oracle has this irritating habit of using the same keywords in all sorts of situations, sometimes benignly, and sometimes not. 'drop table dept CASCADE constraints' does no real damage. It certainly doesn't do any harm to the EMP table, for example. But 'drop user scott CASCADE' wipes out an entire schema without even a by-your-leave!

Alternatively, the day they decide whether to stick to just the one name for all columns currently called things like SEGMENT_NAME, SEG_NAME, or OBJECT_NAME is the day we might expect words like COMPRESS to have unambiguous meanings!!

Regards
HJR (PS, I discussed table compression in my 9i new features eBook. Just thought I'd shamelessly mention it!!). Received on Wed Dec 29 2004 - 17:16:26 CST