1Tree-Structured IndexesLecture 5R & G Chapter 9“If I had eight hours to chop down atree, I'd spend six sharpening my ax.” Abraham LincolnIntroduction•Recall: 3 alternatives for data entries k*:• Data record with key value k• <k, rid of data record with search key value k>• <k, list of rids of data records with search key k>• Choice is orthogonal to the indexing techniqueused to locate data entries k*.• Tree-structured indexing techniques supportboth range searches and equality searches.•ISAM: static structure; B+ tree: dynamic,adjusts gracefully under inserts and deletes.• ISAM = ???Indexed Sequential Access MethodA Note of Caution• ISAM is an old-fashioned idea– B+-trees are usually better, as we’ll see• Though not always• But, it’s a good place to start– Simpler than B+-tree, but many of the same ideas• Upshot– Don’t brag about being an ISAM expert on yourresume– Do understand how they work, and tradeoffs withB+-treesRange Searches• ``Find all students with gpa > 3.0’’– If data is in sorted file, do binary search to find first suchstudent, then scan to find others.– Cost of binary search can be quite high.• Simple idea: Create an `index’ file.– Level of indirection again!* Can do binary search on (smaller) index file!Page 1Page 2Page NPage 3Data Filek2kNk1Index FileISAM• Index file may still be quite large. But we canapply the idea repeatedly!* Leaf pages contain data entries.P0K1P1K2P2KmPmindex entryNon-leafPagesPagesOverflow pagePrimary pagesLeafExample ISAM Tree• Each node can hold 2 entries; no need for`next-leaf-page’ pointers. (Why?)10* 15* 20* 27* 33* 37* 40*46*51*55*63*97*20 33 51 6340Root2Comments on ISAM•File creation: Leaf (data) pages allocatedsequentially, sorted by search key.Then index pages allocated.Then space for overflow pages.•Index entries: <search key value, page id>; they `direct’search for data entries, which are in leaf pages.•Search: Start at root; use key comparisons to go to leaf.Cost log F N ; F = # entries/index pg, N = # leaf pgs•Insert: Find leaf where !data entry belongs, put it there.(Could be on an overflow page).•Delete: Find and remove from leaf; if empty overflowpage, de-allocate.* Static tree structure: inserts/deletes affect only leaf pages.µData PagesIndex PagesOverflow pagesExample ISAM Tree• Each node can hold 2 entries; no need for`next-leaf-page’ pointers. (Why?)10* 15* 20* 27* 33* 37* 40*46*51*55*63*97*20 33 51 6340RootAfter Inserting 23*, 48*, 41*, 42* ...10* 15* 20* 27*33* 37* 40*46*51*55*63*97*20 33 51 6340Root23*48*41*42*OverflowPagesLeafIndexPagesPagesPrimary ... then Deleting 42*, 51*, 97** Note that 51 !!appears in index levels, but 51* not in leaf!10* 15* 20* 27* 33* 37* 40*46* 55*63*20 33 51 6340Root23*48*41*ISAM ---- Issues?• Pros– ????• Cons– ????B+ Tree: The Most Widely Used Index• Insert/delete at log F N cost; keep tree height-balanced. (F = fanout, N = # leaf pages)• Minimum 50% occupancy (except for root). Eachnode contains d <= m <= 2d entries. Theparameter d is called the order of the tree.• Supports equality and range-searches efficiently.Index EntriesData Entries("Sequence set")(Direct search)3Example B+ Tree• Search begins at root, and key comparisonsdirect it to a leaf (as in ISAM).• Search for 5*, 15*, all data entries >= 24* ...* Based on the search for 15*, we know it is not in the tree!Root17 24302*3* 5*7* 14* 16*19* 20*22* 24* 27*29* 33* 34*38*39*13B+ Trees in Practice• Typical order: 100. Typical fill-factor: 67%.– average fanout = 133• Typical capacities:– Height 4: 1334 = 312,900,700 records– Height 3: 1333 = 2,352,637 records• Can often hold top levels in buffer pool:– Level 1 = 1 page = 8 Kbytes– Level 2 = 133 pages = 1 Mbyte– Level 3 = 17,689 pages = 133 MBytesInserting a Data Entry into a B+ Tree• Find correct leaf L.• Put data entry onto L.– If L has enough space, done!– Else, must split L (into L and a new node L2)• Redistribute entries evenly, copy up middle key.• Insert index entry pointing to L2 into parent of L.• This can happen recursively– To split index node, redistribute entries evenly, butpush up middle key. (Contrast with leaf splits.)• Splits “grow” tree; root split increases height.– Tree growth: gets wider or one level taller at top.Example B+ Tree - Inserting 8*Root17 24302*3* 5*7* 14* 16*19* 20* 22* 24* 27*29* 33* 34*38*39*13Example B+ Tree - Inserting 8*v Notice that root was split, leading to increase in height.v In this example, we can avoid split by re-distributingentries; however, this is usually not done in practice.2* 3*Root17243014* 16*19* 20* 22* 24* 27*29* 33* 34*38*39*1357*5* 8*Inserting 8* into Example B+ Tree• Observe howminimumoccupancy isguaranteed inboth leaf andindex pg splits.• Note differencebetween copy-up and push-up;be sure youunderstand thereasons for this.2*3* 5*7*8*5Entry to be inserted in parent node.(Note that 5 iscontinues to appear in the leaf.)s copied up andappears once in the index. Contrast5 24 301713Entry to be inserted in parent node.(Note that 17 is pushed up and onlythis with a leaf split.)……4Deleting a Data Entry from a B+ Tree• Start at root, find leaf L where entry belongs.• Remove the entry.– If L is at least half-full, done!– If L has only d-1 entries,• Try to re-distribute, borrowing from sibling (adjacentnode with same parent as L).• If re-distribution fails, merge L and sibling.• If merge occurred, must delete entry (pointing to Lor sibling) from parent of L.• Merge could propagate to root, decreasing height.Example Tree (including 8*)Delete 19* and 20* ...• Deleting 19* is easy.2* 3*Root17243014* 16*19*20* 22* 24* 27*29* 33* 34*38*39*1357*5* 8*Example Tree (including 8*)Delete 19* and 20* ...• Deleting 19* is easy.• Deleting 20* is done with re-distribution.Notice how middle key is copied up.2* 3*Root173014* 16*33* 34*38*39*1357*5* 8* 22*24*2727* 29* ... And Then Deleting 24*• Must merge.• Observe `toss’ ofindex entry (on right),and `pull down’ ofindex entry (below).3022* 27*29* 33* 34*38*39*2*3*7*14* 16*22*27*29*33* 34*38*39*5* 8*Root3013517Example of Non-leaf Re-distribution• Tree is shown below during deletion of 24*. (Whatcould be a possible initial tree?)• In contrast to previous example, can re-distributeentry from left child of root to right child.Root13517 20223014* 16*17*
View Full Document
Unlocking...