Re: Index compression vs. table compression

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.

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

Index compression is key compression. It applies to the leading edge of the index, or to additional columns, in order, from that point on, if a 'compress X' clause was used during the index creation or rebuilding process, and X is an integer greater than 1. Whatever columns you declared should be compressed are lifted out of the main body of the blocks of the index, and stored in a 'prefix' area -and that's true, without variance, for any block of the index.

Table compression is actually known in the training documentation at least as 'data block compression', and works by removing duplicated data found to be in ANY column(s) for rows that happen to be co-located inside the same Oracle block. You might find that for one block of EMP, for example, ENAME and SAL get 'compressed', because that block happens to have a lot of BOBs and SUEs who all get paid around $1000. But in the very next block of the same table, you might find that a lot of DEPTNOs get 'compressed', because it happens that everyone in that block works in department 20.

For table compression, therefore, you cannot know what columns will be 'suppressed' from block to block. Which is why row ordering before compressing is so important.

Once you get a feel for the fact that we're talking chalk and cheese, then the answer to your specific question becomes obvious: data block compression ONLY applies to tables, so that a tablespace that has COMPRESS as its default storage attribute can quite happily house indexes which will themselves be NOT compressed, since the default won't/can't apply to such segments, and so is just ignored:

SQL> create tablespace X datafile 'x.dbf' size 20M

   2 default compress;

Tablespace created.

SQL> create table T1 as select * from dba_objects; Table created.

SQL> create index I1 on t1(object_id);
Index created.

SQL> analyze table T1 compute statistics; Table analyzed.

SQL> select leaf_blocks from dba_indexes where index_name='I1';

LEAF_BLOCKS

          65

[The index on my new table, housed in a standard tablespace is 65 leaf-blocks big]

SQL> alter index I1 rebuild tablespace X; Index altered.

[I've just rebuilt it into the tablespace which was earlier created to be, by default, a compressing tablespace... so the rebuild works fine, as you can see...]

SQL> analyze table T1 compute statistics; Table analyzed.

SQL> select leaf_blocks from dba_indexes where index_name='I1';

LEAF_BLOCKS

          65

[...and the index is still 65 leaf-blocks big, so the compress attribute has had zilch effect on it].

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. For example, the index will ALWAYS be compressed, if it's built or re-built that way. But a table can start off uncompressed, be altered into compression, and then altered back out of it... and rows inserted into the SAME table at those different times will in turn be uncompressed, compressed and uncompressed. Within the one table, in other words, some blocks will store their data compressed and others won't.

Or again: when you insert index entries into an index (by inserting into the corresponding table, of course), then that insert will ALWAYS pick up the compression attributes of the index. But if you alter a table to be compressed after its initial creation, only those blocks filled with data via direct-load operations will be compressed. Blocks which receive data via conventional DML will be uncompressed: once again, compressed and uncompressed blocks in the same segment.

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 Received on Tue Dec 28 2004 - 21:16:28 CST