Question

What are the benefits of using index file for a relation? Write the advantage(s) of sparse index and dense index file.

Answer 1

Advantages

• Their use in queries usually results in much better performance.
• They make it possible to quickly retrieve (fetch) data.
• They can be used for sorting. A post-fetch-sort operation can be eliminated.
• Unique indexes guarantee uniquely identifiable records in the database.

Sparse indexing allows you to specify the conditions under which a pointer segment is suppressed, not generated, and put in the index database. Sparse indexing has two advantages. The primary one is that it reduces the size of the index, saving space and decreasing maintenance of the index. By decreasing the size of the index, performance is improved. The second advantage is that you do not need to generate unnecessary index entries.

In dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.