The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
0 votes
51 views
What is the Difference between Primary and Clustering Index or both are actually the Same ?
asked in Databases by Active (1.6k points) | 51 views
0
Primary index is built on the field K, then KEY for relation is K and records are sorted on K.

Clustering index is built on the field K, then KEY for relation is X(X!=K) and records are sorted on K.

Atmost one Primary or clustering index is possible as data can be ordered only by 1 field.
0
Sir please elaborate the Clustering X!=K part. is X any other key attribute other than K ?
0
Clustering index means index is built on NON-Key field X and data is ordered on same Non-key field X.

Primary index built on field K :- K is Key and ordering is done on K

Clustering index built on field K :- K is NOT Key and ordering is done on K

Secondary index over Key K:- K is Key and ordering is done on X(some other field rather than K)

Secondary index over NON Key K:-K is Not Key and ordering is done on X(some other field rather than K)
0
@rahul sharma 5,You've mentioned that the clustering index is built on the non-key field. And Primary index is built on the filed K(Key Field).
Suppose I'll take a relation for example,
stud_det(sid,sname,address,course). Here if sid and name together are the composite primary key. Then how will u simultaneously apply primary index,secondary index and clustering index?Could u please elaborate?

2 Answers

0 votes
primary:- Primary clustering are made on using key attribute of the relation. so the actual record and the index file are arranged in same order. while making primary index we generally store the first value of the block so generally its sparse index.

clustering index:- clustering index is maintained for every search key value. these search key value can be repeated and since index is maintained for every search key value the index file become dense.
answered by Active (2.1k points)
0

sonveer .If the file records are physically ordered on a non-key field such as name (which does not have a distinct value for each record) then in that case clustered index is used. 
And the index is maintained for every distinct value of that clustering field. So it is not purely dense or purely sparse, right?

0
yes soumya29. in clustering the field is non-key field as it voilating condition of being key i.e all value must be unique. Now the type of indexing is mix sparse as well as dense because it is not maintained for value (hence not completely dense ) and maintained for every unique value ( hence dense).
0 votes

In Primary Indexing, the Primary Key(P.K.) is used as anchor attribute. It is an example of sparse indexing.

If the number of blocks acquired by index file is n, then Block Access required will be = log2n + 1.

While

In Clustered Indexing, there will be one entry for each unique value of the non-key attribute.

If the number of blocks acquired by index file is n, then Block Access required will be >= log2n + 1.

*differences are many, this is the major one to note.

answered by (227 points)


Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

34,947 questions
41,993 answers
119,239 comments
41,485 users