Column order in composite index
Henry J.
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
important (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.
Mark A
news:0a978bd0-26f9-492e...@m44g2000hsc.googlegroups.com...
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).
--CELKO--
Unfortunately, it does. SQL Server uses a simple B-tree, so you get a
different depth of tree with (state_code, city_name) versus
(city_name, state_code). They concatenate the columns in the order
given, so if the state_code appears first, you get a "tall, deep tree"
for the index. If you give the city_names first, you get a "flat,
wide tree" for the index. The heuristic is to start with the column
with the most values in its range.
Mark A
news:82b00e3e-ca11-4fe0...@y38g2000hsy.googlegroups.com...
> Unfortunately, it does. SQL Server uses a simple B-tree, so you get a
> different depth of tree with (state_code, city_name) versus
> (city_name, state_code). They concatenate the columns in the order
> given, so if the state_code appears first, you get a "tall, deep tree"
> for the index. If you give the city_names first, you get a "flat,
> wide tree" for the index. The heuristic is to start with the column
> with the most values in its range.
Is Sybase the same?
--CELKO--
I think so, but their IQ is a columnar DB architecture and will use
different access methods.