SingleLevelIndexing_examples - NOTES VI File Organizations...

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

View Full Document Right Arrow Icon
NOTES VI File Organizations and Indexing 7 Indexes as Access Paths 8 Types of Single-level Indexes 8.1 Primary Indexes 8.2 Clustering Indexes 8.3 Secondary Indexes
Background image of page 1

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

View Full DocumentRight Arrow Icon
Indexes as Access Paths 1 Introduction - Indexes: access structures o The index is called an access path on the field o Used to speed up the retrieval of records in response to certain search conditions o Indexing fields: used to construct the index - A single-level index is an auxiliary file that makes it more efficient to search for a record in the data file - The index is usually specified on one field of the file (although it could be specified on several fields) - One form of an index is a file of entries <field value, pointer to record>, which is ordered by field value - The index file usually occupies considerably less disk blocks than the data file because its entries are much smaller - A binary search on the index yields a pointer to the file record
Background image of page 2
Example: Given the following data file: EMPLOYEE(NAME, SSN, ADDRESS, JOB, SAL, . .. ) Suppose that: record size R=150 bytes block size B=512 bytes r=30000 records Then, we get: blocking factor Bfr= B div R= 512 div 150= 3 records/block number of file blocks b= (r/Bfr)= (30000/3)= 10000 blocks For an index on the SSN field, assume the field size V SSN =9 bytes, assume the record pointer size P R =7 bytes. Then: index entry size R I =(V SSN + P R )=(9+7)=16 bytes index blocking factor Bfr I = B div R I = 512 div 16= 32 entries/block
Background image of page 3

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

View Full DocumentRight Arrow Icon
number of index blocks b= (r/Bfr I )= (30000/32)= 938 blocks binary search needs log 2 b I = log 2 938= 10 block accesses This is compared to an average linear search cost of: (b/2)= 30000/2= 15000 block accesses If the file records are ordered, the binary search cost would be: log 2 b= log 2 30000= 15 block accesses
Background image of page 4
2 Types of Single-Level Indexes 2.1 Primary Index - Defined on an ordered data file - The data file is ordered on a key field - Includes one index entry for each block in the data file; the index entry has the key field value for the first record in the block, which is called the block anchor o A primary index is an ordered file whose records are of fixed length with two fields. The first field is of the same data type as the ordering key field—called the primary key—of the data file, and the second field is a pointer to a disk block (a block address). We refer to the two field values of index entry i as <K(i), P(i)>. - Examples (refer to figure) o We use the NAME field as primary key, because that is the ordering key field of the file (assuming that each value of NAME is unique). o Each entry in the index has a NAME value and a pointer. The first three index entries are as follows: <K(1) = (Aaron,Ed), P(1) = address of block 1>
Background image of page 5

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

View Full DocumentRight Arrow Icon
<K(2) = (Adams,John), P(2) = address of block 2> <K(3) = (Alexander,Ed), P(3) = address of block 3> - Indexes can also be characterized as dense or sparse. o
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.

This note was uploaded on 09/07/2011 for the course MIS unknown taught by Professor Unknown during the Fall '09 term at New York Institute of Technology-Westbury.

Page1 / 24

SingleLevelIndexing_examples - NOTES VI File Organizations...

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