Notes04 - CS245:DatabaseSystem Principles Notes4:Indexing...

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

View Full Document Right Arrow Icon
CS 245 Notes 4 1 CS 245: Database System  Principles Notes 4: Indexing Hector Garcia-Molina
Background image of page 1

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 2 Indexing  & Hashing value Chapter 4 ? value record
Background image of page 2
CS 245 Notes 4 3 Topics Conventional indexes B-trees Hashing schemes
Background image of page 3

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 4 Sequential File 20 10 40 30 60 50 80 70 100 90
Background image of page 4
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
Background image of page 5

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

View Full DocumentRight Arrow Icon
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
Background image of page 6
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
Background image of page 7

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 8 Comment: {FILE,INDEX} may be contiguous    or not (blocks chained)
Background image of page 8
CS 245 Notes 4 9 Question: Can we build a dense, 2nd level index  for a dense index?
Background image of page 9

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 10 Notes on pointers: (1) Block pointer (sparse index) can be  smaller than record pointer BP RP
Background image of page 10
CS 245 Notes 4 11 (2) If file is contiguous, then we can omit pointers (i.e., compute them) Notes on pointers:
Background image of page 11

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 12 K1 K3 K4 K2 R1 R2 R3 R4
Background image of page 12
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
Background image of page 13

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

View Full DocumentRight Arrow Icon
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)
Background image of page 14
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
Background image of page 15

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 16 Next: Duplicate keys Deletion/Insertion Secondary indexes
Background image of page 16
CS 245 Notes 4 17 Duplicate keys 10 10 20 10 30 20 30 30 45 40
Background image of page 17

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 18 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
Background image of page 18
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
Background image of page 19

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

View Full DocumentRight Arrow Icon
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
Background image of page 20
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!
Background image of page 21

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

View Full DocumentRight Arrow Icon
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
Background image of page 22
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?
Background image of page 23

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

View Full DocumentRight Arrow Icon
CS 245 Notes 4 24 Duplicate values,       primary index Index may point to first  instance of each value only File       Index Summary a a a b .
Background image of page 24
Image of page 25
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/21/2011 for the course CS 245 taught by Professor Dr.hector during the Winter '02 term at SUNY Buffalo.

Page1 / 156

Notes04 - CS245:DatabaseSystem Principles Notes4:Indexing...

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

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