Re: Index: order of fields

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 Fri Feb 25 2000 - 09:48:55 CST