Importance of Column Order in Composite Index

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 - 15:54:35 CST