Interview Questions   Tutorials   Discussions   Programs   

DB2 - What is a clustered index?

asked mar August 20, 2014 12:02 PM  

What is a clustered index?


1 Answers

answered By Mswami   0  
A Clustered index is a type of index where the table records are physically re-ordered to match the index.

Clustered indexes are efficient on columns that are searched for a range of values. After the row with first value is found using a clustered index, rows with subsequent index values are guaranteed to be physically adjacent, thus providing faster access for a user query or an application.

A clustering index determines how rows are physically ordered (clustered) in a table space. Clustering indexes provide significant performance advantages in some operations, particularly those that involve many records. Examples of operations that benefit from clustering indexes include grouping operations, ordering operations, and comparisons other than equal.

You can define a clustering index on a partitioned table space or on a segmented table space. On a partitioned table space, a clustering index can be a partitioning index or a secondary index. If a clustering index on a partitioned table is not a partitioning index, the rows are ordered in cluster sequence within each data partition instead of spanning partitions.

Example: Assume that you often need to gather employee information by department. In the EMP table, you can create a clustering index on the DEPTNO column.


As a result, all rows for the same department are probably close together. DB2 can generally access all the rows for that department in a single read. (Using a clustering index does not guarantee that all rows for the same department are stored on the same page. The actual storage of rows depends on the size of the rows, the number of rows, and the amount of available free space. Likewise, some pages may contain rows for more than one department).
   add comment

Your answer

Join with account you already have



 Write A Tutorials
Online-Classroom Classes

  1 person following this question

  1 person following this tag

  Question tags

Asked 1 year and 1 month ago ago
Number of Views -181
Number of Answers -1
Last updated
2 years and 2 months ago ago

Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!