Primary vs secondary indexes in DBMS Theory - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference in uniqueness between primary and secondary indexes

Which statement correctly describes the uniqueness property of primary and secondary indexes in a database?

A Neither primary nor secondary indexes enforce uniqueness on their indexed columns.

B Secondary indexes enforce uniqueness on the indexed column, while primary indexes do not necessarily enforce uniqueness.

C Both primary and secondary indexes always enforce uniqueness on their indexed columns.

D Primary indexes enforce uniqueness on the indexed column, while secondary indexes do not necessarily enforce uniqueness.

💡 Hint

Think about the role of a primary key in a table.

A primary index is built on the primary key, which uniquely identifies each record, so it enforces uniqueness. Secondary indexes can be built on non-unique columns and do not enforce uniqueness.

📋 Factual

intermediate

Storage location of primary vs secondary indexes

Where are primary and secondary indexes typically stored in relation to the data in a database?

A Both primary and secondary indexes store data sorted with the table rows.

B Secondary indexes store data sorted with the table rows; primary indexes store pointers to the data rows separately.

C Primary indexes store data sorted with the table rows; secondary indexes store pointers to the data rows separately.

D Both primary and secondary indexes store pointers to data rows separately.

💡 Hint

Consider how clustered and non-clustered indexes work.

Primary indexes are often clustered, meaning the data is physically sorted according to the primary key. Secondary indexes are non-clustered and store pointers to the actual data rows.

🚀 Application

advanced

Choosing index type for query optimization

You have a table with millions of records and frequent queries filtering by a non-unique column. Which index type should you create to improve query speed without affecting data uniqueness?

A Create a secondary index on the non-unique column.

B Create a primary index on the non-unique column.

C Create a primary index on a unique column and no secondary index.

D Create no index to avoid overhead.

💡 Hint

Primary indexes require uniqueness; think about which index supports non-unique columns.

Primary indexes require unique columns. For non-unique columns, secondary indexes are used to speed up queries without enforcing uniqueness.

🔍 Analysis

advanced

Impact of primary vs secondary indexes on insert performance

How does having a primary index compared to multiple secondary indexes affect the performance of inserting new records into a database table?

A Secondary indexes slow down inserts more than primary indexes because each index must be updated separately.

B Indexes do not affect insert performance.

C Primary and secondary indexes have the same impact on insert performance.

D Primary indexes slow down inserts more than secondary indexes because they require data sorting.

💡 Hint

Consider how many indexes need updating when inserting a new row.

While primary indexes require maintaining sorted data, secondary indexes require updating multiple separate index structures, which can slow inserts more as the number of secondary indexes grows.

❓ Reasoning

expert

Why secondary indexes can cause inconsistent query results if not maintained properly

In a database system, if secondary indexes are not updated correctly after data changes, what kind of problem can occur when querying using those indexes?

A Queries will always fail with a syntax error due to index corruption.

B Queries may return outdated or incorrect results because the secondary index points to wrong or missing data rows.

C Queries will ignore the secondary index and always perform full table scans.

D Queries will return duplicate rows because secondary indexes enforce uniqueness incorrectly.

💡 Hint

Think about what happens if an index points to data that no longer exists or has changed.

If secondary indexes are not updated after data changes, they can point to wrong or missing rows, causing queries to return outdated or incorrect results.