The Gateway to Computer Science Excellence
0 votes

I am confused about indexing being densed or sparse, Read a lot of answer but can’t figure out

  1. Primary Index is always sparse : since indexing is done of key attribute and records(files) are ordered so we create index only for anchor of the block(group of records).
  2. Secondary index is dense : Since indexing is done on the basis of Non-key/key attribute and records are un-ordered.
  3. Clustering indexing can be sparse or dense: Sparse when we create index for Distinct non-key attributes attributes and there are multiple records having same non-key attributes and Dense when there is unique record with non-key attribute.
  4. B- tree indexing is Dense ; since we have and indexing for every key attribute.
  5. B+ tree is Sparse since indexing is not for every record and is only for some records.

Please correct me If I am wrong at some point and If I am right then please comment.

in Databases by Active (3.2k points) | 44 views

@eyeamgj @adarsh_1997 @prateek Raghuvanshi @Gupta731


Primary Index can be dense/sparse there is no restriction, but sparse is preferred in order to reduce the average memory references.

Clustered index always have to be sparse because the indexing is done on non-key ordered attribute, the values will be repeated, there is no point in using dense index. so indexing is always sparse in this case.

Secondary index on data files is dense where as it is sparse on index files. assume first level has the data blocks which are unordered, so dense index will be used. but whereas in second level, index blocks are present which are ordered and uses key attribute. so sparse index is used



Brother @balchandar reddy san I agree what you said about secondary indexing, But I think that clustered index can be both dense and Sparse for the reason I mentioned above.


Dense when there is unique record with non-key attribute, not sure about this.

Please log in or register to answer this question.

Related questions

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
50,737 questions
57,292 answers
104,910 users