Clustering and Indexing

murugan_mf · Posted: Tue Sep 16, 2008 2:11 pm

Hi all,
Can any one explain Clustering and Indexing with respect to creation of tables and which one is efficient?

vishal_arora · New User Joined: 20 May 2005 Posts: 16

A clustered index is a special type of index that reorders the way records in the table are physically stored.
Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

Clustered index is more efficient . Rows ordered on clustered index makes load as well as retrieval faster

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

A DB2 index is a clustering index if the CLUSTER keyword is specified when the index is created.In a clustered index rows are stored contionously in sequence

When you define a clustering index on a DB2 table, you direct DB2 to insert rows into the table in the order of the clustering key values. The first index that you define on the table serves implicitly as the clustering index unless you explicitly specify CLUSTER when you create or alter another index. For example, if you first define a unique index on |the ACCTID column of the TRANS table, DB2 inserts rows into the TRANS table in the order of the customer account number unless you explicitly define another index to be the clustering index

Eg:CREATE INDEX Indexname
ON tablenam CLUSTER;

murugan_mf · Posted: Tue Sep 16, 2008 5:23 pm

Thanks Prasanth