Log In
1 vote

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 105 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.


i think 4 and 5 are wrong 

b and b+ can be dense or sparse depends on the file if is sorted or not.

if it is sorted then indexing can be done on blocks making it sparse

if it is unsorted then every record have to be in leaf node making it dense

Please log in or register to answer this question.

Related questions

2 votes
0 answers
Here the indexing is done on ordered field so which to use Dense or Sparse because both can be done here ?
asked Jun 29, 2018 in Databases Na462 218 views
3 votes
1 answer
" For any data file, it is possible to construct two separate dense first level indexes on different keys. " How the above statement is true ??
asked Dec 26, 2017 in Databases ashish pal 220 views
2 votes
1 answer
The minimum number of nodes (both leaf and non-leaf) of $B^{+}$ tree index required for storing $5500$ keys and order of $B^{+}$ tree is $8$________________(order is max pointers a node can have) See here first level should be divide by $7$ $2nd$ levelshould divide by ... each $7$ pointer of 1st level has $8$ pointer in 2nd level. Am I missing something?? But in ans they divided by only $8$ :(
asked May 17, 2019 in Databases srestha 562 views
2 votes
1 answer
Consider the following statement below: $A)$ A clustered index may be either sparse or dense. $B)$ Every $B^{+}$ tree index is dense. Which of the above statement is true? Is clustering Index can be dense. Dense means non-ordering field, but clustering field should be ordering field right??
asked May 13, 2019 in Databases srestha 356 views