1 / 21

Announcements

Announcements. Today Single level index review Multilevel index Reading assignment: Chapter 5 and Sections 6.0-6.5. Program 1 post-mortem. I am in the process of grading it Harshness of auto grading Does not compile  grade = 0 We will have some way to replace lowest program

teo
Download Presentation

Announcements

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. Announcements • Today • Single level index review • Multilevel index • Reading assignment: • Chapter 5 and Sections 6.0-6.5

  2. Program 1 post-mortem • I am in the process of grading it • Harshness of auto grading • Does not compile  grade = 0 • We will have some way to replace lowest program • Please follow instructions exactly for turning in subsequent assignment

  3. Review of Single Level Indexes

  4. Primary Index • Index contains one index entry / block • Example index entry for a primary index on student # • <student# = 4173, blockID = 937> • Time to access in I/O operations • Without index: log2 B • With index: log2 Bidx + 1 The student # of the first record on block 937 is 4137 The number of blocks in the file The number of blocks in index

  5. Clustered Index • Index contains one entry / unique value of indexing field • Example index entry for clustered index on major • <major = “CompSci”, blockID = 937> • Time to access in I/O operations • Without index: log2 B • With index: log2 Bidx + 1 The first block with a record whose major is CompSci has blkID=937 The number of blocks in the file The number of blocks in index

  6. Secondary Indexes • Index contains every RID in the file • There are different ways to implement this • Example index entry for secondary index on major • <major = “CompSci”, blockID = 2877> • Time to access in I/O operations • Without index: B • With index: log2 Bidx + 1 + 1 Block 2877 (which is part of the index) contains RIDs for records where major is CompSci The number of blocks in the file The number of blocks in index containing RID list block pointers access the RID list block

  7. Example 1 and 2 in Chapter 14 • Setting file w/ 30,000 fixed length records of 100 bytes on 1024 byte blocks • Records / block = 10 • # blocks in file = 3,000 • Search for key w/o idx: log2 3000 = about 12 • Make index on 9 byte ordering key, block ID is 6 bytes • 68 index entries / block, 3,000 total index entries • 45 blocks in the index • Search for key w/ index = 1+log2 45 = 7 I/Os

  8. Example (continued) • Now search for non-ordering key: • Average is 1500 block reads • Construct secondary key on 9 byte non ordering key field: • Index entry still 15 bytes, 68 entries / block • But we need entry for each of the 30,000 records • Need 442 blocks for index. • Binary search is log2 442 = 9 block reads + 1 to get the record is 10.

  9. Multilevel Indexes

  10. Multilevel Indexes • Consider a clustered index on a non-key field • The index itself is an ordered file of (value, blockPtr) pairs where value is a key field • We can thus construct a primary index on “value” • Add levels until the index fits in one block

  11. Example (continued) • Construct multi level index for previous example • Secondary index had 68 entries / block and required 442 blocks on level 1 • Level two needs ceil(442 / 68) = 7 blocks • Level thee needs ceil(7 / 68) = 1 block • Stop • Search requires 4 block accesses

  12. Inserts / Deletes are Problematic • In general, inserts and deletes to the file requires updating the index • Each level of the index is a physically ordered file thus updates are expensive • One solution: leave some space on each index for inserting new index entries  B+ trees

  13. B+ Trees • B+ trees are the most popular implementation of dynamic multilevel indexes • There are two types B+ tree index blocks • Internal nodes: contain index field values (used to guide the search) and pointers blocks in lower levels of the B+ tree (tree pointers) • Leaf nodes: contain values and matching RIDs

  14. Trees

  15. B+ Tree Properties • A B+ tree is always balanced (ie, distance from the root to leaf is the same for each leaf) • Let p be the maximum number of tree pointers on an internal block • Other than root, each internal page has at least ceil(p/2) tree pointers • A block with q tree pointers has q-1 search field values • Key aspect of B+ trees are graceful algorithms for handling insertions and delete

  16. B+ Tree Insert Overview • Find leaf page on which to add entry • If there is room on leaf page insert and stop • If there is no room, overflow

More Related