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

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

Page1 / 26

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

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