Ordered Indices & B-Trees

CS403 Lecture 38 provides an in-depth investigation into ordered index architectures and tree-based physical storage mechanisms within database management systems. Based on the academic curricula of Catherine Ricardo and Jeffrey A. Hoffer, this lecture analyzes the structural characteristics and operational behavior of clustered versus non-clustered indexes, dense versus sparse indexes, and hierarchical multi-level indices. A clustered index defines the physical sequential ordering of data records stored across disk blocks, ensuring that records with adjacent search key values reside contiguously on disk. Because the physical storage of rows can follow only a single sorted order, a database table can possess at most one clustered index, making it the most efficient mechanism for primary key point queries and range-based retrieval. In contrast, unclustered or non-clustered indexes maintain an independent ordering structure where leaf entries contain search keys and explicit record identifiers pointing to arbitrary physical disk locations, allowing multiple non-clustered indexes per relation. The curriculum further distinguishes dense indices from sparse indices: in a dense index, every search key value present in the data file receives a dedicated index entry, whereas a sparse index contains entries only for selected records, typically storing one entry per physical disk block. While sparse indexes require slightly more processing to locate exact records within a loaded disk page, they drastically reduce index storage volume, enabling substantial portions of the index to reside in fast main memory buffers. When data files expand to hundreds of thousands of tuples, even sparse index files exceed buffer capacity; the lecture demonstrates how multi-level indexing resolves this scaling dilemma by constructing sparse higher-level outer indices over base index blocks. Students also examine index maintenance algorithms, studying how insertion and deletion operations dynamically update dense and sparse index entries to prevent search degradation. Furthermore, the lecture investigates composite search key indexing, exploring how equality constraints and multi-attribute range bounds are evaluated against multi-column index keys. Regularly featured in Virtual University CS403 final term past papers by Moaaz and Waqar Siddhu, mastering ordered index structures equips learners to engineer highly scalable, optimized database storage systems.