Re: Key Compression vs. Selectivity

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

    Correct, and you didn't overlook anything.

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

    It doesn't need a grain of salt added - it already contains     its own. If I had said "you should not move..." you would     be quite right (and excessively polite) to say that a grain of     salt is needed. I even had the words "perhaps" leading the     statement.

    The preceding sentence says: "then perhaps you could     rearrange the column order to maximise the compression"

    How much more tentative does a suggestion have to be before     it ISN'T coming across as a mindless directive ?

    You are correct, regarding "compression might still yield better     performance" - but if you have an I/O problem, then is is possible     that you haven't even got as far as working out a suitable set of     indexes - let alone being at the point where you worry about     column ordering :-) There are ALWAYS a few more considerations.

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