MVSFORUMS.com

Catherine · Posted: Mon Mar 05, 2007 8:55 am Post subject: Clustered Vs Non-Clustered Index

Hi,

Can you help me in underastanding about clustered indexes with an example? Why are they efficient than non-clustered indexes?

Thanks in advance,
Catherine

kolusu · Posted: Mon Mar 05, 2007 9:14 am Post subject:

catherine,

When a table has a clustering index, an INSERT statement inserts the records as nearly as possible in the order of their index values.Clustered inserts can provide significant performance advantages in some operations, particularly those that involve many records, such as grouping, ordering, and comparisons other than equal. Although a table can have several indexes, only one can be a clustering index.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

bauer · Posted: Tue Mar 06, 2007 3:06 am Post subject:

Catherine,

and additional: if there are a lot of changes on the columns of the cluster index, a reorg is necessary. Otherwhise the index has the clusterattribute but the clusterrate is so poor, that db2 doesn't habe any advantage of the clusterindex.

regards,
bauer

Catherine · Posted: Wed Mar 14, 2007 8:35 am Post subject: Clustered Vs Non-Clustered Index

Hi,

Let me know if im right in my understanding

Consider this table EMP. Say Emp_id is the Primary Key. Region is Unique

Emp_id Dept Region
1 100 INDIA
3 200 USA
4 100 MALASIA
2 100 AFRICA
5 200 DUBAI

1)A clustered index has to be created only on the Primary Key Emp_id

2)A clustered Index cannot be created on Dept or Region.

3)There can be several Unique Indexes for a table, but only One clustered index

4) I dont understand clearly how is the data stored in a clustered Index
Can u explain to me how data is stored in this index with this example above?

Im confused.Plz help!!

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Hello Catherine,

"clustering" is a property of an index, and only one index of the many indexes one table has can have this attribute set.

To 1) and 2): A clustering index can be created on any column or columns, but you can choose only one index.

To 3): Yes

To 4): The index itself is stored as any other index on a table. Special is the behaviour of DB2, if you INSERT a row into the table. The INSERT will look, which index is defined as clustering, locate the page of the index key which is next to the new one and then store the new row into the same data page.

regards
Christian