Key Compression vs. Selectivity

Hi,

I'd like to bounce off some thoughts of you to verify that my basic reasoning is correct.

The general rule of thumb for indexing is to put the most selective columns first in order to foster fast lookups. (Of course there are other considerations involved, which single index covers most queries etc.)

So at first sight it would seem that index compression and this rule of thumb exclude each other because index compression has most benefits if there are multiple rows per compressed prefix which is more likely to happen if less selective columns are put first.

Thinking a bit further about this, key compression starts to save space if there are at least several rows /on a block/ that share the the same prefix. In other words if the most selective column(s) is / are not unique and still has several rows per value key compression will yield space usage (and thus potentially speed) improvements.

Thanks

        robert Received on Fri Dec 15 2006 - 03:58:55 CST