Test your understanding of indexing concepts covered in this section.
What is the main reason databases use B+ Trees instead of BSTs?
- A) B+ Trees are easier to implement
- B) B+ Trees minimize disk I/O by having high fan-out and shallow height
- C) B+ Trees support only equality lookups
- D) B+ Trees store data in memory
Answer: B Explanation: B+ Trees have a high fan-out (many keys per node), making the tree very shallow (~3-4 levels for millions of records). Each node fits in one disk page, minimizing the number of disk reads. A BST with the same data would require ~20 levels (log₂(n)), meaning more disk I/O.
How many clustered indexes can a table have?
- A) 0
- B) 1
- C) 2
- D) Unlimited
Answer: B Explanation: A table can have only one clustered index because the clustered index determines the physical order of data on disk. Since data can only be stored in one physical order, only one clustered index is possible.
What is a covering index?
- A) An index that covers all columns of the table
- B) An index that contains all columns required by a query, eliminating table access
- C) An index on a single column
- D) An index that is created automatically
Answer: B Explanation: A covering index includes all columns referenced by a query. The database can satisfy the query entirely from the index without accessing the table (index-only scan), making it much faster.
Which index type is best for low-cardinality columns in an OLAP database?
- A) Hash index
- B) Bitmap index
- C) Clustered index
- D) Partial index
Answer: B Explanation: Bitmap indexes store bitmaps for each distinct value and use boolean operations (AND, OR, NOT) to combine them. They are excellent for low-cardinality columns (gender, status, region) in data warehouse environments. They are less suitable for OLTP due to high update costs.
Given a composite index on (A, B, C), which queries can use it?
- A)
WHERE B = 1 AND C = 2 - B)
WHERE A = 1 AND C = 2 - C)
WHERE A = 1 AND B = 2 AND C = 3 - D) Both B and C
Answer: D Explanation: A composite index on (A, B, C) can be used when the query includes A (the leftmost column). Query C uses all three columns. Query B uses A for filtering but may not use C efficiently. Query A does not include A, so the index cannot be used (leftmost prefix rule).
What happens when a B+ Tree leaf node overflows?
- A) The tree is rebuilt from scratch
- B) The node splits into two nodes, and the middle key moves to the parent
- C) The database throws an error
- D) The leaf node is deleted
Answer: B Explanation: When a leaf node exceeds its maximum capacity, it splits into two nodes. The keys are evenly distributed, and the middle key is promoted to the parent node. If the parent also overflows, the split propagates upward.
Why are sequential primary keys (auto-increment) more efficient than UUIDs for B+ Tree inserts?
- A) UUIDs are larger, causing more disk I/O
- B) Sequential keys insert at the right edge, minimizing page splits
- C) UUIDs cause the tree to become unbalanced
- D) Sequential keys require less memory
Answer: B Explanation: Auto-increment keys always insert at the rightmost edge of the B+ Tree, causing page splits only on the rightmost leaf. UUIDs insert at random positions, causing splits throughout the tree, which leads to pages being only about 50% full and much higher write overhead.
What is the difference between a hash index and a B+ Tree index?
- A) Hash indexes support range queries; B+ Trees do not
- B) Hash indexes are O(1) for equality lookups but cannot do range scans
- C) B+ Trees use hashing internally
- D) There is no difference
Answer: B
Explanation: Hash indexes provide O(1) lookup for exact match queries (WHERE key = 'value') but cannot handle range queries (WHERE key > 100), pattern matching, or sorting. B+ Trees support all these operations with O(log n) performance.
Premium Content
Unlock Indexing Quiz and all premium lessons with a subscription.
From ₹199.99/year — See plans