Re: Key Compression vs. Selectivity

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 18 Dec 2006 09:36:44 +0100
Message-ID: <4un28lF18tumiU1@mid.individual.net>

On 17.12.2006 23:21, DA Morgan wrote:

> Jonathan Lewis wrote:

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

:-) I have meditated over the second link over the weekend and I am not sure I understand the reasoning behind the last sentence: "The most critical point, perhaps, is that you should avoid moving a column that is typically used with a range scan towards the front of the index."

Is it because there will potentially be a lot of index pages visited and thus the CPU overhead can be significant? Or did I overlook something else?

If this *is* in fact the reasoning then I think it should come with a grain of salt: if your app is IO bound and you have plenty CPU resources at hand then compression might still yield better performance - even for range scans.

Kind regards

        robert Received on Mon Dec 18 2006 - 02:36:44 CST