Mastering Multiway Merge Sort and B+ Trees: A CSC370 Database Tutorial

Introduction to Database Performance Optimization

In today's data-driven world, sorting large datasets efficiently is critical—whether you're analyzing TikTok trends, processing real-time stock market feeds, or managing AI training data. The CSC370 assignment on multiway merge sort and B+ trees teaches foundational skills for database indexing and query optimization. This tutorial breaks down the key concepts using the assignment's exercises as a guide, without solving them directly.

Understanding Multiway Merge Sort (MWMS)

Phases and I/O Cost Calculation

Multiway merge sort is used when data exceeds available memory. In the assignment, we have a relation of 10,000,000 blocks (4KB each) and memory for only 320 blocks. The disk has a seek time of 10 ms, rotational latency of 5 ms, and transfer time of 1 ms per block, totaling 16 ms per I/O. The first phase creates sorted runs of 320 blocks each, requiring 10,000,000 / 320 = 31,250 runs. Each run is written and read once, so phase 1 costs 2 * 10,000,000 * 16 ms = 320,000,000 ms = 320,000 seconds. In subsequent phases, we perform 319-way merges. The number of runs reduces by a factor of 319 each phase: after phase 2, runs = ceil(31,250 / 319) = 98; after phase 3, runs = ceil(98 / 319) = 1. So total phases = 3. The cost of phases 2 and 3 includes reading and writing all blocks each time: phase 2 reads 10,000,000 blocks and writes 10,000,000 blocks (since merging produces a new run of the same size), costing 2 * 10,000,000 * 16 ms = 320,000 s. Phase 3 does the same: another 320,000 s. Total I/O cost = 3 * 320,000 s = 960,000 s ≈ 11.1 days. This illustrates why efficient merge sort is crucial for big data.

Building a B+ Tree Index

Insertion Sequence Example

B+ trees are used in database indexes to speed up searches. With n=3 (each node holds up to 2 keys and 3 pointers), insert keys in order: 2, 15, 31, 32, 6, 18, 19, 20, 3, 4, 5, 40, 41, 42. Start with an empty root. Insert 2: root = [2]. Insert 15: [2,15]. Insert 31: [2,15,31] overflows; split into left [2,15] and right [31], promote 15 to new root: root=[15], children: left=[2,15], right=[31]. Insert 32: go to right child [31], insert -> [31,32] (ok). Insert 6: go to left child [2,15], insert -> [2,6,15] overflows; split left child into [2] and [6,15], promote 6 to root: root becomes [6,15], children: [2], [6,15], [31,32]. Continue similarly for remaining keys. Each insertion may cause splits, and the tree grows balanced. After all insertions, the tree has height 2 or 3. This process is analogous to updating a leaderboard in a gaming app like Fortnite, where new scores are inserted and rankings adjust.

Query Plan Cost Estimation

In the assignment, a query plan involves selections and a join. The left selection result is small and kept in memory; the right selection is pipelined. The cost is measured in I/Os. Without the exact statistics from queryeval.pdf, we can reason: the left selection reads its input blocks (say B_left) and writes none if kept in memory. The right selection reads its input blocks (B_right) and produces sorted sublists on the fly (pipelined to the join). The join then merges the sorted left input (in memory) with the pipelined right input. The total I/O cost is approximately the cost of reading both base tables minus any savings from pipelining. For example, if B_left = 1000 blocks and B_right = 5000 blocks, cost ≈ (1000 + 5000) * 16 ms = 96,000 ms = 96 seconds. This mirrors how modern AI apps like ChatGPT optimize query execution to reduce latency.

Concurrency Control: Locking Schedules

Shared and Exclusive Locks

Consider schedule 1: r1(A); r2(B); r3(C); r1(B); r2(C); r3(D); w1(A); w2(B); w3(C). We insert shared locks (SL) before reads not followed by a write of the same element by the same transaction, and exclusive locks (XL) otherwise. For T1: r1(A) gets SL(A); r1(B) gets SL(B); w1(A) gets XL(A) (upgrade from SL). For T2: r2(B) gets SL(B); r2(C) gets SL(C); w2(B) gets XL(B). For T3: r3(C) gets SL(C); r3(D) gets SL(D); w3(C) gets XL(C). Unlocks at end. When run, T1 holds SL(A) and SL(B); T2 requests SL(B) but must wait because T1 holds SL(B) (shared locks are compatible, so T2 gets SL(B) immediately; but later T2 requests XL(B) and must wait for T1 to release SL(B). T3 similarly waits. This can lead to deadlock if not handled. The same analysis applies to schedules 2, with upgrading and update locks.

Logging and Recovery: UNDO and REDO Logs

Consider the UNDO log sequence: [START S] [S,A,60] [COMMIT S] [START T] [T,A,10] [START U] [U,B,20] [T,C,30] [START V] [U,D,40] [V,F,70] [COMMIT U] [T,E,50] [COMMIT T] [V,B,80] [COMMIT V]. For a nonquiescent checkpoint after [S,A,60], the [END CKPT] is written after the next commit (COMMIT S). For each crash point, we must scan backward to find incomplete transactions (those without COMMIT). For REDO logs, the [END CKPT] can be written at any point after the checkpoint start; crashes require redoing from the last checkpoint. This is similar to how apps like Instagram recover from crashes to maintain data consistency.

Normalization and BCNF Decomposition

For relation R(A,B,C,D) with FD's AB→C, B→D, CD→A, AD→B. Check BCNF: compute candidate keys. From AB→C and B→D, we get AB→CD, so AB is a key. Also CD→A and AD→B suggest other keys. Check each FD: AB→C: AB is a key, so OK. B→D: B is not a key (since B alone does not determine all attributes), so violation. Decompose: R1(B,D) with FD B→D; R2(A,B,C) with FD AB→C and implied FD? R2 is in BCNF if key is AB. Similarly for other FD's. This process ensures data integrity, crucial for financial systems like banking apps.

Conclusion

Mastering these database concepts—multiway merge sort, B+ trees, locking, logging, and normalization—equips you to build efficient, reliable systems. Practice with the assignment exercises to deepen your understanding, and apply these skills to real-world applications like social media platforms, AI databases, or e-commerce sites.