Regarding index compression

Question

I've searched about index compression on the Internet. But could not find an answer.

My question is;

How does Index compression work for new entries in the index segment?

Answer 1

(Keeping this to the topic of basic compression, ie, the one that doesn't need the advanced compression license)

The index consists of two types of blocks - branch and leaf.

Branch blocks are always "compressed" in the sense that we do our best to store as little as possible but still be able to successfully point to the next level in the tree (branch or leaf). We strip as much of the suffix off the key without impacting the integrity of the pointers. For example, if a branch block needed to the distinguish between two keys of "SMITH" and "ADAMS", then storing "S" and "A" would suffice, rather than the full keys.

Leaf blocks are compressed if you nominate the compress prefix factor when creating/rebuilding the index. This is a simple de-duplication mechanism (because if you go into more "serious" compression mechanics you'll pay a CPU price). For example, if you had three keys on (last,first) names of

(SMITH,JOHN) (SMITH,MIKE) (JONES,SUE)

then leading compression of 1 would create a structure along the lines of:

PREFIX: SMITH, points to entries JOHN and MIKE PREFIX: JONES, points to SUE

and thus you are only storing "SMITH" once per leaf rather than twice.

So in terms of index maintenance, not much difference to normal index maintenance, ie, a row comes in and we need to make appropriate changes to the impacted lead blocks. If its a non-compressed index, the full key goes into the leaf. If its a compressed index, we'll split it into the compressed prefix and the remainder and adjust the leaf block accordingly.