| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Index: order of fields
Jonathan Lewis wrote:
>
> 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.
Interestingly I had a similar debate a year or so ago because Oracle Designer used to reverse the index order (which prompted our analyst to exclaim "if Designer does it this way, it must be right")
Cheers
--
We are born naked, wet and hungry...then things get worse Received on Mon Mar 13 2000 - 05:12:37 CST
 |
 |