"Henry J." <tank209209@[EMAIL PROTECTED]
> wrote in message
news:0a978bd0-26f9-492e-a868-921ffbc31cea@[EMAIL PROTECTED]
> I'm pretty new to DB2 and like to know if column cardinality can play
> a role in choosing the column order in a composite index.
>
> In Oracle, it appears composite indexes of different column orderings
> perform the same regardless of column cardinality (http://
> asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:
> 5671539468597):
>
> ..... for a query that references all of A, B, C the above three
> indexes will perform more or less the same -- regardless of the
> selectivity of A, B, or C. Benchmark in the book ;)
>
> In SQL Server, however, it is claimed that column ordering is very
> im****tant (http://www.mssqlcity.com/Tips/tipInd.htm):
>
> If you create a composite (multi-column) index, try to order the
> columns in the key as to enhance selectivity, with the most selective
> columns to the leftmost of the key.
>
> Can anybody comment on what we should expect from DB2? Is there any
> official info on this topic from IBM? I tried but couldn't find much
> info on this topic from IBM.
>
> Thanks a lot.
So long as you supply all 3 columns of the index in the predicate, it
doesn't make much of a difference in DB2. I doubt that it makes a
difference
in SQL Server either (although I don't that for sure).
The problem is when you only supply values in the predicate for part of
the
index, and even worse when you don't supply the leftmost columns of the
index in the predicate (in which case the b-tree will not be used and a
complete index scan will be used, or a table scan will be used).
|
