Dense vs sparse indexes in DBMS Theory - Quick Revision & Key Differences

Recall & Review

beginner

What is a dense index in database indexing?

A dense index has an index entry for every record in the data file. This means each search key value appears in the index along with a pointer to the actual record.

beginner

What is a sparse index in database indexing?

A sparse index has index entries only for some records, usually one per data block or page. It points to the first record in each block, not every record.

intermediate

How does a dense index affect search speed compared to a sparse index?

A dense index allows faster searches because it has an entry for every record, so the exact record can be found quickly. A sparse index may require scanning within a block after locating the pointer.

intermediate

What is a key advantage of using a sparse index?

A sparse index uses less storage space and requires less maintenance because it stores fewer index entries, making it more efficient for large datasets where full indexing is costly.

intermediate

In which scenario is a dense index preferred over a sparse index?

Dense indexes are preferred when fast, direct access to every record is needed, such as in primary key indexing or when records are frequently searched individually.

Which type of index contains an entry for every record in the data file?

A Dense index

B Sparse index

C Clustered index

D Hash index

A dense index has an index entry for every record, unlike a sparse index which has fewer entries.

What is a main benefit of a sparse index compared to a dense index?

A Faster search for every record

B Uses less storage space

C Has an entry for every record

D Requires more maintenance

Sparse indexes use less storage because they store fewer entries.

If you want quick access to every record, which index type should you choose?

A Sparse index

B No index

C Dense index

D Bitmap index

Dense indexes provide direct pointers to every record, enabling fast access.

Sparse indexes usually have entries pointing to:

A The first record in each block

B Every record

C Random records

D Only deleted records

Sparse indexes point to the first record in each data block or page.

Which index type requires scanning within a block after locating the pointer?

A B-tree index

B Dense index

C Hash index

D Sparse index

Sparse indexes may require scanning inside the block because they do not have entries for every record.

Explain the difference between dense and sparse indexes in simple terms.

Think about how many records each index points to.

When would you choose a sparse index over a dense index? Give reasons.

Consider trade-offs between speed and storage.