8,080 views
3 votes
3 votes
Primary index vs Secondary Index

Primary index is done on the primary key of the database.Secondary indexing is done on the candidate key..and clusterd index on non key field.

Which indexing required dense and sparse indexing and why ?

WHy cant secondary indices be done based on sparse indexing as it is order on candidate key and which also orders the file?

1 Answer

Best answer
9 votes
9 votes
First of all we have to understand the difference between dense and sparse index. Dense index is called dense because the records are arranged based on an unordered attribute and hence the records are not sorted. So for searching a key, pointer from index to every record is required.

A sparse index is called so because we need less no of pointers from index to records of database as the records are arranged based on ordered key and hence we can quickly access the record by going to block first and then access the following records without having individual index for each of the records.

Primary indexing or clustered indexing means indexing is done based on an ordered field and that should be a candidate key of the relation and the physical ordering of the records in disk is based on this order. So, a table can have only one primary/clustered index though this can be changed by reordering the records based on a new clustered index anytime. So, primary/clustered index can easily be sparse.

Now secondary index is any index that is not primary. So secondary index must be dense index as the index is guaranteed not to be in the same order as the physical order of records. Secondary index can be either built over key or non key attribute.
selected by

Related questions

0 votes
0 votes
0 answers
1
0 votes
0 votes
1 answer
3
vishnu777 asked Nov 8, 2022
391 views
Can anyone explain in detail about the concepts of sencondary indexing in dbms??
0 votes
0 votes
0 answers
4
Purple _ rain asked Sep 3, 2022
136 views
What is fanout ratio