Notes04 - 1 CS 245: Database System Principles CS 245 Notes...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 CS 245: Database System Principles CS 245 Notes 4 1 Notes 4: Indexing Hector Garcia-Molina Indexing & Hashing value Chapter 4 value record CS 245 Notes 4 2 value value Topics Conventional indexes B-trees Hashing schemes CS 245 Notes 4 3 Hashing schemes Sequential File 20 10 40 30 60 50 CS 245 Notes 4 4 60 80 70 100 90 Sequential File 20 10 40 30 60 50 Dense Index 10 20 30 40 50 60 CS 245 Notes 4 5 60 80 70 100 90 70 80 90 100 110 120 Sequential File 20 10 40 30 60 50 Sparse Index 10 30 50 70 90 110 CS 245 Notes 4 6 60 80 70 100 90 130 150 170 190 210 230 2 Sequential File 20 10 40 30 50 Sparse 2nd level 10 30 50 70 90 110 10 90 170 250 330 CS 245 Notes 4 7 60 80 70 100 90 110 130 150 170 190 210 230 410 490 570 Comment: {FILE,INDEX} may be contiguous or not (blocks chained) CS 245 Notes 4 8 Question: Can we build a dense, 2nd level index for a dense index? CS 245 Notes 4 9 Notes on pointers: (1) Block pointer (sparse index) can be smaller than record pointer CS 245 Notes 4 10 BP RP (2) If file is contiguous, then we can omit pointers (i.e., compute them) Notes on pointers: CS 245 Notes 4 11 K1 K3 K2 R1 R2 R3 CS 245 Notes 4 12 K4 R4 3 K1 K3 K2 R1 R2 R3 say: 1024 B per block CS 245 Notes 4 13 K4 R4 if we want K3 block: get it at offset (3-1)1024 = 2048 bytes Sparse vs. Dense Tradeoff Sparse: Less index space per record can keep more of index in memory Dense Can tell if any record exist CS 245 Notes 4 14 Dense: Can tell if any record exists without accessing file (Later: sparse better for insertions dense needed for secondary indexes) Terms Index sequential file Search key ( primary key) Primary index (on Sequencing field) CS 245 Notes 4 15 Secondary index Dense index (all Search Key values in) Sparse index Multi-level index Next: Duplicate keys Deletion/Insertion CS 245 Notes 4 16 Deletion/Insertion Secondary indexes Duplicate keys 10 10 20 10 CS 245 Notes 4 17 20 30 20 30 30 45 40 10 10 20 10 10 10 10 20 10 10 20 10 10 10 10 20 Dense index, one way to implement? Duplicate keys CS 245 Notes 4 18 20 30 20 30 30 45 40 20 20 30 30 30 20 30 20 30 30 45 40 20 20 30 30 30 4 10 10 20 10 10 20 30 40 Dense index, better way? Duplicate keys CS 245 Notes 4 19 20 30 20 30 30 45 40 40 10 10 20 10 10 10 20 30 Sparse index, one way? Duplicate keys CS 245 Notes 4 20 20 30 20 30 30 45 40 30 10 10 20 10 10 10 20 30 Sparse index, one way? Duplicate keys ooking r 30! CS 245 Notes 4 21 20 30 20 30 30 45 40 30 careful if l for 20 o 10 10 20 10 10 20 30 30 Sparse index, another way? Duplicate keys place first new key from block CS 245 Notes 4 22 20 30 20 30 30 45 40 30 10 10 20 10 10 20 30 30 Sparse index, another way?...
View Full Document

Page1 / 26

Notes04 - 1 CS 245: Database System Principles CS 245 Notes...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online