Re: Importance of Column Order in Composite Index

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:<hNHD7.1889$Fm5.1208866_at_news1.sttln1.wa.home.com>...
> Actually if you rebuild the index with the compress option version i is very
> good. The compress option (assuming 8.1.6 at least) will compress repeated
> values in a key. This can have great space savings and thus speed up the
> usefullness of the index.
> Jim
> "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

I believe that the correct answer to which ordering of the index columns, x, y, & z as either xyz or zyx depends on the question:

How many queries are that that will provide only x and only z values in retrieval of a table row? Since Oracle can normally only use the index to solve the query when the leading column is provided then if z is the only keyed column in the query zyx is the better choice as it eliminates the need to have two indexes. In the case of x being the only column used to retrive the row data, since x is not very distinct a full table scan will probably be more effective than using the index.

Obviously when all 3 values are present there is no real advantage to either order with version 7 on up (nor probably in 6 either), but I suspect that in general you will find that the most distinct key column will be used to query without the presence of the other key columns in the real world so that zyx is the better choice.

• Mark D Powell --