MadeEasyDb

Question

Which one is true?

a.  Secondry index over key must be dense.

b. Clustering index must be dense.

c. primary index must be sparse.

d.both a and c

Comments

Ans is A

can we classify the index type of Clustering as Sparse or dense ?? i don't think so it is neither dense nor sparse.

am i right?

---

If we don't have sparse index over primary key, it defeats the purpose of creating a index. If we have dense index for primary key we are just creating an other table ehich contain primary key that is pointing to the record containing same primary key.

@OneZero

no, its not like that. having dense index on primary key doesn't defeat it's purpose.

its beneficial even after that.

this is because, in general. a disk block contains more number of key pointer pair than that of number of records. so searching for records in the index block will in general takes less time as compared to that in record block, because for searching the same key, less number of index block access is required, as compared to that of record block access(if no index is there), irrespective of whether the primary index is dense or sparse.

and we are not just creating another table containing primary key.  it has its own purpose... number of block access is decreased(except in the case when key pointer pair size is equal to the record size).

---

@aambazinga 

Thanks :)