B-Trees

1. B-Trees

2. Outline • Motivation • B-Tree • B+Tree • Insertion • Deletion

3. Disks are slow! • So far, data fits in RAM • If data is too large, must store on Disk • Big-Oh analysis changes (“constant time”?) • Pentium (‘90s): 500 MIPS • IDE HDD (’00s): 7200 RPM = 120 accesses per second • 1 disk access = 4.000.000 instructions • Number of disk accesses dominates the running time! • In reality, the numbers are even worse (multi-*) We are willing to do lots of CPU work to reduce disk access!

4. Example • Analyse the following case: • You are asked to implement a database program that stores the data of all phonebook entries in Jakarta, e.g. there are 5.000.000 entries. • Each entry contains: name, address, telephone number, etc. Assume each entry fits in a recordof 512 bytes. • Total file size = 5.000.000 * 512 byte = 2,4GB. • Assume: • CPU: 25 million instructions per second • Disk: 6 accesses per second

5. Disk blocks • When storing data on disks, we use blocks: • Secondary storage is divided into blocks of equal size. Common sizes: 512 bytes, 2 KB, 4 KB, 8 KB, etc. • A block is the smallest unit transferred between disk & memory. Although a program reads 10 bytes, it must access 1 whole block.

6. Using Binary Search Trees • Unbalanced binary search is a disaster. 5.000.000 disk accesses = 9.6 days! • Average BST: 1.38 log N = 30 accesses = 5 seconds • Randomly, expect nodes 3x deeper = 15 seconds • Red-black tree: worst case = 1.44 log N = 32 accesses = 5.3 seconds • How can we do better than this?

7. Make fatter trees! • Complete binary tree = log2N nodes • Complete M-ary tree = logMN nodes • For N=31, best binary has height = 5, 5-ary tree has height = 3

8. B-Tree • B-Tree of order M is an M-ary tree with following properties: • Data items stored at leaves • Non-leaf nodes store M-1 keys: key i represents smallest key in subtree i+1. • Root is either leaf or has between 2 and M children. • All non-leaf nodes (except root) have between M/2 and M children. • All leaves are at the same level. < >= 1250 1291 625 1000 1425 2000 1277 1282

9. B+Tree • B+Tree is a variant of B-Tree: • All key values (data) are stored at leaf nodes. • Add pointer to connect leaf nodes as a linked-list. • Enables sequential access of data without requiring tree traversal. • Internal nodes contain keys, and are used as index.

10. B+Tree Leaf Nodes < >= 1250 0625 1000 1425 2000 1425 1600 1000 1250 1300 2000 0350 0625

11. B+Tree Node Structure A high level node (internal node) K K K .. . . . . . P P P P n-1 n-1 n 1 1 2 2 Pointer to subtree for keys< K Pointer to subtree for keys>= K & < K Pointer to subtree for keys>= K & < K Pointer to subtree for keys>= K n-2 n-1 n-1 1 1 2 A leaf node (Every key value appears in a leaf node) K K K . . . . . . . P P P P n-1 n-1 n 1 1 2 2 Pointer to leaf with smallest key greater than K Pointer to record (block) with key K Pointer to record (block) with key K Pointer to record (block) with key K n-1 n-1 1 2

12. Example of a B+Tree Leaf Nodes < >= 1250 0625 1000 1425 2000 1000 1250 1300 1425 1600 2000 0350 0625 0350 0625 1000 2000 1250 1425 1300 1600 Actual Data Records

13. Queries on B+Trees • Find all records with a search-key value of k. • Start with the root node • Examine the node for the smallest search-key value > k. • If such a value exists, assume it is Kj. Then follow Pi to the child node • Otherwise k Km–1, where there are m pointers in the node. Then follow Pm to the child node. • If the node reached by following the pointer above is not a leaf node, repeat the above procedure on the node, and follow the corresponding pointer. • Eventually reach a leaf node. If for some i, key Ki = k follow pointer Pi to the desired record (or bucket). Else no record with search-key value k exists.

14. Queries on B+Trees: Range Query • Find all records with a search-key value > k and < l (range query). • Find all records with a search-key value of k. • while the next search-key value < l, follow the corresponding pointer to the records. • when the current search-key is the last search-key in a node, follow the last pointer Pn to the next leaf node.

15. Insertion on B+Trees • Find the leaf node in which the search-key value would appear • If the search-key value is already there in the leaf node (non-unique search-key), • record is added to data file, and • if necessary search-key and the corresponding pointer is inserted into the leaf node

16. Insertion on B+Trees • If the search-key value is not there, then add the record to the data file: • If there is room in the leaf node, insert (key-value, pointer) pair in the leaf node (should be sorted) • Otherwise, split the node (along with the new (key-value, pointer) entry) as shown in the next slides. • Splitting a node: • Take the new (search-key value, pointer) pairs (including the one being inserted) in sorted order. Place the first M/2 in the original node, and the rest in a new node. • When splitting a leaf, promote the middle/median key in the parent of the node being split, but retain the copy in the leaf. • When splitting an internal node, promote the middle/median key in the parent of the node being split, but DO NOT retain the copy in the leaf. • If the parent is full, split it and propagate the split further up.

17. 89 89 67 18 123 67 89 123 18 67 89 123 Building a B+Tree B+Tree of order 4 (4-ary tree) Insert 67, 123, 89, 18, 34, 87, 99, 104, 36, 55, 78, 9 promote but retain a copy Root =leaf node split data records The split at leaf nodes root node < why promote 89, not 67? >= data records

18. 89 89 89 67 87 123 123 18 18 34 34 67 Building a B+Tree 67, 123, 89, 18,34, 87, 99, 104, 36, 55, 78, 9 root node < >= split root node 67 89 < >=

19. 67 67 87 87 18 18 34 34 Building a B+Tree 67, 123, 89, 18, 34, 87,99, 104, 36, 55, 78, 9 root node split 67 89 < >= 89 99 123 root node 67 89 104 < 89 99 104 123

20. promote & don’t retain a copy 67 67 87 87 36 67 89 104 The split at non-leaf nodes 36 55 Building a B+Tree 67, 123, 89, 18, 34, 87, 99, 104,36, 55, 78, 9 double node split split 67 89 104 < 18 34 36 89 99 104 123 split The splitting of nodes proceeds upwards till a node that isn’t full is found. In the worst case: root node may be split increasing the height of the tree by 1. 89 104 36 67 < 18 34 89 99 104 123

21. Deletion on B+Trees • Remove (search-key value, pointer) from the leaf node • If the node has too few entries due to the removal (minimum requirement: M/2 children), and the entries in the node and a sibling fit into a single node, then • Merge the two nodes into a single node • Delete the pair (Ki–1, Pi), where Pi is the pointer to the deleted node, from its parent, recursively using the above procedure.

22. Deletion on B+Trees • Otherwise, if the node has too few entries due to the removal, and the entries in the node and a sibling does not fit into a single node, then • Redistribute the pointers between the node and a sibling such that both have more than the minimum number of entries. • Update the corresponding search-key value in the parent of the node. • The node deletions may cascade upwards till a node which has M/2  or more pointers is found.

23. Summary • B-Tree is mostly used as an external data structure for databases. • B-Tree of degree m has the following properties: • All non-leaf nodes (except the root which is not bound by a lower limit) have between M/2 and M children. • A non-leaf node that has n branches will contain n-1 keys. • All leaves are at the same level, that is the same depth from the root. • B+Tree is a variant from B-Tree where all key values are stored in leaves