Quick question about index key compression

[peter_strauss]

c.f. Note 1109743

Hello,

After applying index key compression to an index, are newly added or changed entries also compressed?

Or do you have to keep re-compressing the index as the index content changes.

Kind regards,

Peter

[stefan_koehler · Accepted solution]

Hello Peter,

> After applying index key compression to an index, are newly added or changed entries also compressed?

Yes .. Oracle does a "live-compression", so every newly insert/changed on compressed key columns will be compressed.

I have also verified this with some block dumps on an oracle 10.2.0.4 database .. unfortunately i didn't extend my blog post with this. If you want to take a closer look by your own, just perform some index leaf block dumps.

The interpretation of block dumps can be viewed here:

/people/stefan.koehler/blog/2008/04/21/oracle-index-key-compression

Regards

Stefan

[peter_strauss · Accepted solution]

Hello Stefan,

Great! Thanks for your help, and for your blog entries.

Kind regards,

Peter

[Former Member]

Hi Peter,

You don't need to re-compress your index when the index content changes.

I'm using Oracle Index key compression in Production (and in other 8 different SAP systems) from May 2009 .... it's a great feature!

Regards,

Federico Biavati

[peter_strauss]

Hi Federico,

Thanks for the useful information.

I'll leave the thread open in case anyone has something to add.

I guess the newly added data must get added to the index with the data compressed, but other than that the change is the same as for when he index is not compressed.

Kind regards,

Peter