A database may hold millions of rows, yet a well-designed query returns in milliseconds. The secret is indexing — an auxiliary data structure that lets the DBMS jump straight to the rows it needs instead of scanning the whole table. Around indexing sit two more heavily-tested topics: how records are physically stored and organized on disk, and RAID, the disk-array technology that gives database servers both speed and fault tolerance. Together they answer the classic exam question: "How does a database find data fast and keep it safe?"
This trio appears again and again in BCS, Bank IT-officer, and government IT recruitment written exams and vivas. Expect one-mark MCQs (which RAID gives no redundancy? what is stored in a B+ tree leaf?) and short-answer questions (dense vs sparse index; clustered vs non-clustered). This lesson builds every idea from first principles so you can answer all of them.
Learning objectives
- Explain the purpose of an index and the trade-off it makes.
- Distinguish dense vs sparse, primary vs secondary, and clustered vs non-clustered indexes.
- Describe the structure and search cost of B-tree and B+ tree, and why databases prefer them.
- Explain hash indexes and bitmap indexes and when each fits.
- Decide when an index helps and when it hurts.
- Compare file organizations: heap, sequential, hashed.
- Lay out the storage hierarchy of a DBMS.
- Choose the right RAID level (0, 1, 5, 10) for a database workload.
- Read a simple query plan and see how indexes drive query optimization.