Index / Primary Key

[Jerry]

Is that true that one table can only have one clustered index? The
column with clustered index will be sorted physically, is that true?
The column with non-clustere index will not be sorted physically, is
that true?

Also, is primary key clustered index by default?

Thanks!

[Mark A]

"Jerry" <je*********@hotmail.com> wrote in message
news:8c**************************@posting.google.c om...

Is that true that one table can only have one clustered index? The
column with clustered index will be sorted physically, is that true?
The column with non-clustere index will not be sorted physically, is
that true?

Also, is primary key clustered index by default?

Thanks!

A very common point of confusion.

All indexes are sorted in the exact order of the index. By designating a
clustering index, you are specifying the order of the rows in the associated
table (the order of which DB2 tries to maintain during an insert and after a
reorg of the table). So by that definition, there can only be one clustering
index per table.

In DB2 for LUW, there is no clustering index by default. In DB2 for z/OS,
the first index defined is the clustering index unless another index is
explicitly defined as the clustering index.

[Ian]

Mark A wrote:

"Jerry" <je*********@hotmail.com> wrote in message
news:8c**************************@posting.google.c om...

Is that true that one table can only have one clustered index? The
column with clustered index will be sorted physically, is that true?
The column with non-clustere index will not be sorted physically, is
that true?

Also, is primary key clustered index by default?

Thanks!

A very common point of confusion.

All indexes are sorted in the exact order of the index. By designating a
clustering index, you are specifying the order of the rows in the associated
table (the order of which DB2 tries to maintain during an insert and after a
reorg of the table). So by that definition, there can only be one clustering
index per table.

It is true that a table can have only 1 clustering index.

HOWEVER:

DB2 UDB V8.x for LUW has a new feature called multi-dimensional
clustering (MDC) which allows you to define multiple dimensions
(groups of columns) and DB2 will *guarantee* that the table is
clustered along each dimension.

This feature effectively allows you to have more than one clustering
index (but the physical implementation is completely different).

[Mark A]

"Ian" <ia*****@mobileaudio.com> wrote in message
news:42**********@newsfeed.slurp.net...

HOWEVER:

DB2 UDB V8.x for LUW has a new feature called multi-dimensional
clustering (MDC) which allows you to define multiple dimensions
(groups of columns) and DB2 will *guarantee* that the table is
clustered along each dimension.

This feature effectively allows you to have more than one clustering
index (but the physical implementation is completely different).

No, there is only one MDC per table.

Ian: Do you let you children play with matches?

[dc]

"Mark A" <no****@nowhere.com> wrote in
news:AL********************@comcast.com:

"Jerry" <je*********@hotmail.com> wrote in message
news:8c**************************@posting.google.c om...

Is that true that one table can only have one clustered index? The
column with clustered index will be sorted physically, is that true?
The column with non-clustere index will not be sorted physically, is
that true?

Also, is primary key clustered index by default?

Thanks!

A very common point of confusion.

All indexes are sorted in the exact order of the index. By designating
a clustering index, you are specifying the order of the rows in the
associated table (the order of which DB2 tries to maintain during an
insert and after a reorg of the table). So by that definition, there
can only be one clustering index per table.

In DB2 for LUW, there is no clustering index by default. In DB2 for
z/OS, the first index defined is the clustering index unless another
index is explicitly defined as the clustering index.

One slight nuance on the stmt "In DB2 for z/OS, the first index defined
is the clustering index unless another index is explicitly defined as the
clustering index. "

It is technically the oldest remaining index on the table that is the
default cluster index. Meaning if you create a table withi idx01 and
dont specify a CI and then add idx02 at a later date, the clustering is
on idx01. If you subsequently drop idx01 and re-create it and don't
specify idx01 as the CI then the data will be clustered on idx02.

This sometimes burns people.

[Mark A]

"dc" <no**@non.com> wrote in message
news:Xn*************************@199.45.49.11...

One slight nuance on the stmt "In DB2 for z/OS, the first index defined
is the clustering index unless another index is explicitly defined as the
clustering index. "

It is technically the oldest remaining index on the table that is the
default cluster index. Meaning if you create a table withi idx01 and
dont specify a CI and then add idx02 at a later date, the clustering is
on idx01. If you subsequently drop idx01 and re-create it and don't
specify idx01 as the CI then the data will be clustered on idx02.

This sometimes burns people.

Clustering indexes should be explicitly defined, which can be done with an
alter in DB2 for z/OS (anyone in Toronto listening?).

The biggest problem with not explicitly defining the clustering index is
that you will often have the index for the PK as the clustering index by
default, since it is often the first index created. But if the PK is a
single column, then it is usually a very poor choice for the clustering
index.