Re: Key Compression vs. Selectivity

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:4uf9uoF17a65cU1_at_mid.individual.net...
>
> 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

Richard,

The "most selective first" has always been a little flexible (at least since 6.0 appeared) and your basic reasoning is correct.

A couple of references you might want to look at:

    http://www.jlcomp.demon.co.uk/ch_07.html

        (see the item on index compression) and

    http://www.jlcomp.demon.co.uk/faq/compress_ind.html

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html