Re: Index: order of fields

Sorry it's taken so long to respond to this. I too have read (usually in Oracle manuals) that there is a benefit in putting the most selective column first.

However, I can come up with no logical
or arithmetical reason which justifies the claim. (In fact it is clearly untrue when you move to 8.1 and consider the effect of compressed indexes.).

I am inclined therefore to believe that this is one of those details that used to be true in Oracle 5, (in this case because of the compression algorithm they used
on indexes) but ceased to be true in
subsequent releases.

I would be interested in hearing from
anyone who can describe how to prove
that 'most selective first' is best; or has a reference to a relevant document.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

markp7832_at_my-deja.com wrote in message <896895$r03$1_at_nnrp1.deja.com>...
>In article <951160637.4541.1.nnrp-10.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>> is this something you've picked up from a whitepaper
>> somewhere, or you own experimentation ? I'd be
>> interested to see the proof and the assumptions its
>> based on.
>>
>> --
>>
>> Jonathan Lewis
>> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>>
>> markp7832_at_my-deja.com wrote in message <88ruko$df2
>$1_at_nnrp1.deja.com>...
>> >In article <1104_951125428_at_way>,
>>
>> >1) When all columns will always be present order them with the most
>> >selective, i.e., columns with the most distinctive values first.
>This
>> >improves the efficiency of b-trieve balancing maintenance.
>>
>This statement is based on a combination of my limited understanding of
>B-trieve theory and past readings. I no longer have the souces at my
>desk, but this was one of the few points that several consultants
>including at least one from Oracle who were part of a canned product
>implementation effort back in 1995 could agree on.
>
>I do plead guilty to using the term 'balancing maintenance' loosly as I
>include not just pure maintenance during the insert and updates but to
>the general balance and performance of the index in general.
>
>From the ver 7 Appl Developers Guide ch 5 section How to Choose
>Composite Indexes:
>
>"If a composite index is to be used by queries based on multiple column
>values, ordering these columns from most selective to least selective
>in the CREATE INDEX statement best improves query performance."
>
>--
>Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Sun Mar 12 2000 - 12:38:12 CST