Column order of an Index

[cmitchell]

Does the column order in an index matter? Currently i have an index
with the columns in the following order: Type, Product_oid. If i
create a new index with the columns in the reverse order: Product_oid,
type will it make a difference? Also what is the best way to tell if
an index is being used (I am using DB2 UDB v8.1.5 for Windows). Thanks
in advance..

[Serge Rielau]

cmitchell wrote:

Does the column order in an index matter? Currently i have an index
with the columns in the following order: Type, Product_oid. If i
create a new index with the columns in the reverse order: Product_oid,
type will it make a difference? Also what is the best way to tell if
an index is being used (I am using DB2 UDB v8.1.5 for Windows). Thanks
in advance..

It absolutely matters.
DB2 can still exploit an index if only the first n-elements apear in the
query. That implied that columns which are not often accessed, yet you
want them to be part of the index, should be at the end.
All things being equal it is benefiical to place the column with the
highest selectivity in the front.
So: (Gender, Name) would be really bad :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab