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]

Re: Index / Primary Key

"Jerry" <jerryhz2000@ho tmail.com> wrote in message
news:8c811045.0 506241525.28dbc e6d@posting.goo gle.com...[color=blue]
> 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![/color]

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]

Re: Index / Primary Key

Mark A wrote:[color=blue]
> "Jerry" <jerryhz2000@ho tmail.com> wrote in message
> news:8c811045.0 506241525.28dbc e6d@posting.goo gle.com...
>[color=green]
>>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![/color]
>
>
> 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.[/color]

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]

Re: Index / Primary Key

"Ian" <ianbjor@mobile audio.com> wrote in message
news:42bd7d9e$1 _4@newsfeed.slu rp.net...[color=blue]
>
> 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).
>[/color]
No, there is only one MDC per table.

Ian: Do you let you children play with matches?

[dc]

Re: Index / Primary Key

"Mark A" <nobody@nowhere .com> wrote in
news:ALedneCgvd W-BSHfRVn-uQ@comcast.com:
[color=blue]
> "Jerry" <jerryhz2000@ho tmail.com> wrote in message
> news:8c811045.0 506241525.28dbc e6d@posting.goo gle.com...[color=green]
>> 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![/color]
>
> 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.
>
>[/color]

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]

Re: Index / Primary Key

"dc" <none@non.com > wrote in message
news:Xns9695E68 192A27nonenonec om@199.45.49.11 ...[color=blue]
>
> 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.
>[/color]

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.