Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Importance of Column Order in Composite Index

Re: Importance of Column Order in Composite Index

From: Howard J. Rogers <howardjr_at_www.com>
Date: Wed, 31 Oct 2001 09:15:58 +1100
Message-ID: <3bdf2757$0$3561$afc38c87@news.optusnet.com.au>


Indexing as X, Y, Z might actually be preferable, because with the low selectivity columns first, you can use index key compression, and get much more compact indexes (and hence much lower I/O) than you might otherwise manage. That's assuming you have at least 8i, of course.

The old myth about the most selective column needing to be first is just that -a myth. Hasn't been true for ages. Whatever the order, the space in the leaf nodes is going to be the same, and the only benefit of having Z first would be a potential saving in branch nodes because of the possible trimming of branch entries that can take place -but that's probably not even going to be noticeable.

9i has skip scanning of indexes which means searches for non-leading keys can be done efficiently, so that shouldn't be a major issues there.

And if you select for X, and X isn't in the index at all, I doubt the Z,Y index will be touched.

Just my first thoughts. I may have seconds, though.

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"David Franit" <dfranit_at_hotmail.com> wrote in message
news:e3b48e1f.0110301354.97a7a16_at_posting.google.com...

> Hello,
>
>
> In Oracle/SAP systems many of the default indexes are setup such
> that the rows with low selectivity form the first columns of a composite
> index. Frequently,this matches the primary key. For example,
>
> Table ABC has 2,000,000 rows.
>
> Column #X has 3 distinct values.
> Column #Y has 30 distinct values.
> Column #Z has 10000 distinct values.
>
> (i) A default index on table ABC might contain column X, followed by
column Y
> and column Z.
>
> (ii) If somebody were to create a new index, they would like create one
> with column Z followed by column Y, and, probably, leave out column X.
>
> To what extent will the index given in case in case (ii) be preferable
> to that of case #(i)?
>
> Assume that the main select statement on table ABC is
> governed by ' WHERE X = .. and Y = ... and Z = ...' and that the
> statement is one of the most heavily used SELECT statements in the
> system. Also, assume poor clustering and that we cannot change index
> #(i), but that we can create index #(ii).
>
> I realize that that index #(ii) is preferable to that of #(i), but I
> am not sure whether or not it will greatly improve the performance
> of the SELECT statement.
>
> Any information or data based on actual experience would be greatly
> appreciated.
>
>
>
>
> Thanks,
> D. Franit
Received on Tue Oct 30 2001 - 16:15:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US