9,479 views

A clustering index is defined on the fields which are of type

1. non-key and ordering
2. non-key and non-ordering
3. key and ordering
4. key and non-ordering

if file records are physically ordered on a nonkey field- which does not have a distinct value for each record-that field is called the clustering field. We can create a different type of index, called a clustering index. This differs from a primary index, which requires that the ordering field of the data file have a distinct value for each record.

Primary indexing – key , ordered

Secondary indexing – Key , Unordered

Clustering Indexing – Non-key , Ordered

Secondary non key indexing – Non key , Unordered

Check this out for detailed explanation and more such questions, hope it will help.

Truth Table for this

K.   O          PK

K    U          SK(ordered)

NK O          CLUSTERED

NK U          SK (unordered)

### Subscribe to GO Classes for GATE CSE 2022

There are several types of ordered indexes. A primary index is specified on the ordering key field of an ordered file of records. Recall from Section 17.7 that an ordering key field is used to physically order the file records on disk, and every record has a unique value for that field. If the ordering field is not a key field- that is, if numerous records in the file can have the same value for the ordering field— another type of index, called a clustering index, can be used. The data file is called a clustered file in this latter case. Notice that a file can have at most one physical ordering field, so it can have at most one primary index or one clustering index, but not both

Reference -> Database Systems book BY Navathe, $6^{th}$ Edition, 18.1 Types of Single- Level Ordered Indexes Page no. 632.

A clustered index can be created on any attribute set, which is ordered (but maximum one for a table). By default, it is created for the primary key. We can change this to any non-key also. So, (A) and (C) can have clustered index. I guess the question assumes primary index as not being called clustered index, so answer should be A.

http://msdn.microsoft.com/en-us/library/ms186342.aspx

by

If file records are physically ordered on a nonkey field—which does not have a distinct value for each record—that field is called the clustering field and the data file. We can create a different type of index, called a clustering
index, to speed up retrieval of all the records that have the same value for the clustering field. This differs from a primary index, which requires that the ordering field
of the data file have a distinct value for each record.

Page No 636, Navathe 6th Edition

This is from Navathe book too ! So does it make option A stronger ?

There are several types of ordered indexes. A primary index is specified on the
ordering key field of an ordered file of records. Recall from Section 17.7 that an
ordering key field is used to physically order the file records on disk, and every record
has a unique value for that field. If the ordering field is not a key field—that is, if
numerous records in the file can have the same value for the ordering field—
another type of index, called a clustering index, can be used. The data file is called a
clustered file in this latter case. Notice that a file can have at most one physical
ordering field, so it can have at most one primary index or one clustering index, but
not both.

Ref -> Database Systems book BY Navathe, 6th Edition , 18.1 Types of Single-Level Ordered Indexes Page no 632

As per this book Primary Index is not clustering index !

Clustering index is an index whose search key also defines the sequential order of the file. Clustering indices are also called primary indices. The primary index may appear to denote the index of an primary key; but can be built on any search key. The serach key of an clustering index is also called primary key;although not necessarily ... so search key can be non key value and it works on sequential manner means ordered ... so option A ..

### 1 comment

By definition given in KORTH, ANSWER is both A) and C).

But by the definition given in Navathe,it is option A)

### 1 comment

@Poshita Shrivastava any explanation ?

1
12,110 views
2
8,156 views
3
13,008 views