1 / 122

CS143: Index

Explore dense vs. sparse index, primary vs. secondary index, tree-based vs. hash-based index, B+-tree, hash-based index, extending hashing, and more. Learn query processing and index insertion in database systems.

mweir
Download Presentation

CS143: Index

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS143: Index

  2. Topics to Learn • Important concepts • Dense index vs. sparse index • Primary index vs. secondary index (= clustering index vs. non-clustering index) • Tree-based vs. hash-based index • Tree-based index • Indexed sequential file • B+-tree • Hash-based index • Static hashing • Extendible hashing

  3. Basic Problem • SELECT *FROM StudentWHERE sid = 40 • How can we answer the query?

  4. Random-Order File • How do we find sid=40?

  5. Sequential File • Table sequenced by sid. Find sid=40?

  6. Binary Search • 100,000 records • Q: How many blocks to read? • Any better way? • In a library, how do we find a book?

  7. Basic Idea • Build an “index” on the table • An auxiliary structure to help us locate a record given a “key” 40

  8. 110 70 50 30 10 90 100 80 60 40 20 120 10 30 90 70 50 80 60 40 20 100 Dense, Primary Index Dense Index Sequential File • Primary index (clustering index) • Index on the search key • Dense index • (key, pointer) pair for every record • Find the key from index and follow pointer • Maybe through binary search • Q: Why dense index? • Isn’t binary search on the file the same?

  9. Why Dense Index? • Example • 10,000,000 records (900-bytes/rec) • 4-byte search key, 4-byte pointer • 4096-byte block. Unspanned tuples • Q: How many blocks for table (how big)? • Q: How many blocks for index (how big)?

  10. 90 50 10 130 150 110 70 30 10 30 50 70 90 100 60 40 20 80 Sparse, Primary Index Sequential File Sparse Index • Sparse index • (key, pointer) pair per every “block” • (key, pointer) pair points to the first record in the block • Q: How can we find 60?

  11. Sequential File Sparse 2nd level 1st level 130 330 170 10 210 10 50 490 90 170 70 570 110 30 230 410 150 250 90 190 90 70 50 30 10 100 80 60 40 20 Multi-level index Q: Why multi-level index? Q: Does dense, 2nd level index make sense?

  12. 30 20 80 100 90 50 70 40 10 60 Secondary (non-clustering) Index Sequence field • Secondary (non-clustering) index • When tuples in the table are not ordered by the index search key • Index on a non-search-key for sequential file • Unordered file • Q: What index? • Does sparse index make sense?

  13. 100 30 20 80 90 60 40 10 50 70 90 30 ... 20 80 100 Sparse and secondary index?

  14. 90 30 20 80 100 50 70 40 10 60 10 50 10 60 20 50 30 70 90 ... ... 40 Secondary index sparse High level • First level is always dense • Sparse from the second level

  15. Important terms • Dense index vs. sparse index • Primary index vs. secondary index • Clustering index vs. non-clustering index • Multi-level index • Indexed sequential file • Sometimes called ISAM (indexed sequential access method) • Search key ( primary key)

  16. 35 Insertion Insert 35 10 10 20 30 30 40 60 40 50 60 Q: Do we need to update higher-level index?

  17. 15 20 Insertion Insert 15 10 10 30 30 40 60 40 50 60 Q: Do we need to update higher-level index?

  18. 20 15 20 30 Insertion Insert 15 10 10 30 40 60 40 50 60 Q: Do we need to update higher-level index?

  19. 32 39 38 31 34 35 36 Main index Potential performance problem After many insertions… 10 20 30 33 40 50 60 70 80 overflow pages (not sequential) 90

  20. Traditional Index (ISAM) • Advantage • Simple • Sequential blocks • Disadvantage • Not suitable for updates • Becomes ugly (loses sequentiality and balance) over time

  21. B+Tree • Most popular index structure in RDBMS • Advantage • Suitable for dynamic updates • Balanced • Minimum space usage guarantee • Disadvantage • Non-sequential index blocks

  22. 20 30 80 90 B+Tree Example (n=3) root 70 Non leaf 80 50 Leaf 70 50 ... ... ... Balanced: All leaf nodes are at the same level

  23. Sample Leaf Node (n=3) From a non-leaf node 20 30 Last pointer: to the next leaf node points to tuple • n: max # of pointers in a node • All pointers (except the last one) point to tuples • At least half of the pointers are used. • (more precisely, (n+1)/2 pointers)

  24. Sample Non-leaf Node (n=3) 23 56 To keys 56 k To keys k<23 To keys 23 k<56 • Points to the nodes one-level below • - No direct pointers to tuples • At least half of the ptrs used (precisely, n/2) • - except root, where at least 2 ptrs used

  25. 20 30 80 90 Search on B+tree 70 • Find 30, 60, 70? 80 50 70 50 • Find a greater key and follow the link on the left • (Algorithm: Figure 12.10 on textbook)

  26. 5 8 10 5 5 8 10 5 8 Nodes are never too empty • Use at least Non-leaf: n/2 pointers Leaf: (n+1)/2 pointers full node min. node Non-leaf Leaf n=4

  27. Number of Ptrs/Keys for B+tree Max Max Min Min Ptrs keys ptrs keys Non-leaf (non-root) n n-1 n/2 n/2-1 Leaf (non-root) n n-1 (n+1)/2 (n-1)/2 Root n n-1 2 1

  28. B+Tree Insertion (a) simple case (no overflow) (b) leaf overflow (c) non-leaf overflow (d) new root

  29. (a) Simple case (no overflow)

  30. 20 30 80 90 Insertion (Simple Case) • Insert 60 70 80 50 70 50

  31. 20 30 80 90 Insertion (Simple Case) • Insert 60 70 80 50 70 50 60

  32. (b) Leaf overflow

  33. 20 30 50 60 80 90 Insertion (Leaf Overflow) • Insert 55 70 80 50 70 • No space to store 55

  34. 20 30 80 90 Insertion (Leaf Overflow) • Insert 55 70 80 50 Overflow! 70 60 50 55 • Split the leaf into two. Put the keys half and half

  35. 20 30 80 90 50 55 Insertion (Leaf Overflow) • Insert 55 70 80 50 70 60

  36. 20 30 80 90 50 55 Insertion (Leaf Overflow) • Insert 55 70 80 50 60 70 60 • Copy the first key of the new node to parent

  37. 20 30 80 90 50 55 Insertion (Leaf Overflow) • Insert 55 70 No overflow. Stop 80 50 60 70 60 • Q: After split, leaf nodes always half full?

  38. (c) Non-leaf overflow

  39. 20 30 50 60 50 55 Insertion (Non-leaf Overflow) • Insert 52 70 60 Leaf overflow. Split and copy the first key of the new node

  40. 20 30 50 60 50 52 Insertion (Non-leaf Overflow) • Insert 52 70 55 60

  41. 55 20 30 50 60 50 52 Insertion (Non-leaf Overflow) • Insert 52 70 55 60

  42. 20 30 50 52 Insertion (Non-leaf Overflow) • Insert 52 70 Overflow! 50 55 60 55 60

  43. 20 30 50 52 Insertion (Non-leaf Overflow) • Insert 52 70 50 55 60 55 60 Split the node into two. Move up the key in the middle.

  44. 20 30 50 52 Insertion (Non-leaf Overflow) • Insert 52 70 55 Middle key 50 60 55 60

  45. 20 30 50 52 55 70 Insertion (Non-leaf Overflow) • Insert 52 No overflow. Stop 50 60 55 60 Q: After split, non-leaf at least half full?

  46. (d) New root

  47. 20 30 50 60 50 55 Insertion (New Root Node) • Insert 25 60

  48. 50 60 50 55 20 25 Insertion (New Root Node) • Insert 25 Overflow! 30 30 60

  49. 50 55 20 25 Insertion (New Root Node) • Insert 25 Split and move up the mid-key. Create new root 30 50 60 30 60

  50. 50 55 20 25 Insertion (New Root Node) • Insert 25 • Q: At least 2 ptrs at root? 50 60 30 30 60

More Related