Re: Key Compression vs. Selectivity

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 17 Dec 2006 14:21:59 -0800
Message-ID: <1166394116.541313@bubbleator.drizzle.com>

Jonathan Lewis wrote:

> "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

Thank you for responding. I was convinced I'd be wrong if I offered any advice: And in that I was partially correct. <g>

Thanks for the links.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Dec 17 2006 - 16:21:59 CST