Re: Importance of Column Order in Composite Index

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