Prev: How to avoid 2nd trigger
Next: Any suggestion on how to speed up large batch inserts?
From: Henry J. on 13 Jul 2008 22:46
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
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.
From: Mark A on 13 Jul 2008 23:19
"Henry J." <tank209209(a)yahoo.com> wrote in message
news:0a978bd0-26f9-492e-a868-921ffbc31cea(a)m44g2000hsc.googlegroups.com...
> 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
> 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.

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).


From: --CELKO-- on 14 Jul 2008 11:02
>> I doubt that it makes a difference in SQL Server either (although I don't that for sure). <<

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.
From: Mark A on 14 Jul 2008 14:32
"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:82b00e3e-ca11-4fe0-a920-3022440c98c7(a)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?


From: --CELKO-- on 14 Jul 2008 15:51
>> Is Sybase the same? <<

I think so, but their IQ is a columnar DB architecture and will use
different access methods.

 |  Next  |  Last
Pages: 1 2
Prev: How to avoid 2nd trigger
Next: Any suggestion on how to speed up large batch inserts?