454.3-09.IndexStructures

454.3-09.IndexStructures - Database Systems II Index...

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

View Full Document Right Arrow Icon
CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 111 Database Systems II Index Structures CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 112 Introduction We have discussed the organization of records in secondary storage blocks. Records have an address, either logical or physical. But SQL queries reference attribute values, not record addresses. SELECT * FROM R WHERE a=10; How to find the records that have certain specified attribute values?
Background image of page 1

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

View Full DocumentRight Arrow Icon
CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 113 Introduction recordID1 recordID2 . . . value value value matching records index blocks holding records value CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 114 Index-Structure Basics Storage structures consist of files. Data files store, e.g., the records of a relation. Search key : one or more attributes for which we want to be able to search efficiently. Index file over a data file for some search key associates search key values with pointers to (recordID = rid) data file records that have this value. Sequential file : records sorted according to their primary key.
Background image of page 2
CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 115 Index-Structure Basics Sequential File 20 10 40 30 60 50 80 70 100 90 CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 116 Index-Structure Basics Three alternatives for data entries k*: - record with key value k - <k, rid of record with search key value k> - <k, list of rids of records with search key k> Choice is orthogonal to the indexing technique used to locate entries k* Two major indexing techniques: - tree-structures - hash tables.
Background image of page 3

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

View Full DocumentRight Arrow Icon
CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 117 Index-Structure Basics Dense index : one index entry for every record in the data file. Sparse index : index entries only for some of the record in the data file. Typically, one entry per block of the data file. Primary index : determines the location of data file records, i.e. order of index entries same as order of data records. Secondary index does not determine data location. Can only have one primary index, but multiple secondary indexes. CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 118 Index-Structure Basics 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 4
CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 119 Index-Structure Basics 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 CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 120 Index-Structure Basics 10 10 20 10 30 20 30 30 45 40 10 20 30 40 Duplicate key values sparse index data entry for first new key from block
Background image of page 5

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

View Full DocumentRight Arrow Icon
CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 121 Index-Structure Basics Sparse index: - requires less index space per record, - can keep more of index in memory, - needed for secondary indexes. Dense index:
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 36

454.3-09.IndexStructures - Database Systems II Index...

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

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