There is no single best storage design — only the one that fits your queries
This closing lesson steps back across every structure this unit covered, comparing them side by side, and ends with the practical discipline of choosing and tuning indexes for a real, mixed workload — closing the entire course.
After this lesson
You should be able to
- Compare heap, sorted, hash, and tree-based organization by their insert cost and search capability.
- Choose an appropriate indexing strategy for a described, realistic query workload.
Every structure in this unit is a different point on the same trade-off
Heap files insert cheaply but require a full scan to search, with no index at all. Sorted files search fast via binary search but pay heavily on insert, shifting data to preserve order. Hash indexes give near-instant exact-match search and reasonable insert cost, but cannot serve range queries at all. B+ trees give good, predictable performance for both exact-match and range queries, at somewhat higher overhead per operation than a specialized structure tuned for only one query shape.
None of these is a mistake to avoid — each is the right tool for a specific, named workload, exactly the discipline CS205ES's very first lesson opened this entire two-course arc with: know what you optimize for before choosing a structure.
Performance tuning: choosing indexes deliberately, not everywhere
Given a real workload, tuning starts by identifying the columns actually used in WHERE clauses, JOIN conditions, and ORDER BY clauses — from Unit 3's SQL — since those are exactly where an index pays off. A column rarely searched, or a small table cheap to scan fully, often does not need an index at all — remembering the write-overhead trade-off from earlier in this unit.
The choice of index type follows directly from this unit's comparisons: a hash index for a column always searched by exact equality, a B+ tree (the common default) for a column also searched by range or needing sorted order, and a clustered index reserved for the single column most central to the table's typical access pattern, per the earlier lesson's reasoning.
From a folder of files to a tuned, indexed database — the whole course, in one line
Unit 1 opened by asking why files alone fail. Unit 2 turned entities into disciplined tables. Unit 3 gave you the language to query them and the rigor to design them without redundancy. Unit 4 guaranteed that many people can safely use that database at once, even through a crash. This unit closes the loop: the actual, physical machinery — files, blocks, and indexes — that makes all of the above fast enough to matter in the real world.
Try it yourself
A Course table is rarely updated but is searched constantly, both by exact course_id lookup and by range queries on credit_hours. Propose an indexing plan, naming which index type goes on which column and why.
Need a hint?
One column needs exact-match speed only; the other needs range support. This table's low update frequency also means index write-overhead is barely a concern here.
Check the worked solution
course_id, searched only by exact match, is well served by a hash index — fast and simple, with no range need. credit_hours, searched by range, needs a B+ tree, the only structure among those covered that supports range queries efficiently. Since the table updates rarely, the write-overhead cost of maintaining both indexes is a non-issue, making this an easy call in favor of indexing both columns.
Quick check
Why is there no single "best" file organization or index type that a database should always use?
Why this lesson exists
Syllabus mapping
Comparison of File Organizations · Indexes and Performance Tuning
Maps to course outcome CO4.