7Index - INDEXING (CHAPTER 12) 1 01/11/09 TOPICS Basic...

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

View Full Document Right Arrow Icon
09/27/09 1 INDEXING (CHAPTER 12)
Background image of page 1

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

View Full DocumentRight Arrow Icon
09/27/09 2 TOPICS Basic concepts Hashing B + -tree
Background image of page 2
09/27/09 3 INTRODUCTION Review Conceptual Logical Physical E-R data model Relational data model SQL relation = a file Org. of records on a disk page Organization of attributes within a record Index Files
Background image of page 3

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

View Full DocumentRight Arrow Icon
09/27/09 4 Software Architecture of a DBMS File System Buffer Pool Manager Abstraction of records Index structures Query Interpretor Query Optimizer Relational Algebra operators: , σ , ρ , δ , , , , ÷ , - Query Parser
Background image of page 4
09/27/09 5 Implementation of б Emp table: б Salary=30,000 (Employee) Process the select operator using a file scan (linear scan) F1 = Open the file corresponding to Employee P = read first page of F1 While P is not null For each record in P, if the record satisfies the selection predicate then produce as output P = read next page of F1 /* P becomes null when EoF is reached */ SS# Name Age Salary dno 1 Joe 24 20000 2 2 Mary 20 25000 3 3 Bob 22 27000 4 4 Kathy 30 30000 5 5 Shideh 4 4000 1 SS# Name Age Salary dno 4 Kathy 30 30000 5
Background image of page 5

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

View Full DocumentRight Arrow Icon
09/27/09 6 Implementation of б Emp table: б Salary=30,000 (Employee) P = read first page of F1 While P is not null For each record in P, if the record satisfies the selection predicate then produce as output P = read next page of F1 SS# Name Age Salary dno 1 Joe 24 20000 2 2 Mary 20 25000 3 3 Bob 22 27000 4 4 Kathy 30 30000 5 5 Shideh 4 4000 1 SS# Name Age Salary dno 4 Kathy 30 30000 5 Fetch the page from disk if not in the buffer pool
Background image of page 6
09/27/09 7 Implementation of б Emp table: б Salary=30,000 (Employee) Process the select operator using a file scan (linear scan) F1 = Open the file corresponding to Employee P = read first page of F1 While P is not null For each record in P, if the record satisfies the selection predicate then produce as output P = read next page of F1 SS# Name Age Salary dno 1 Joe 24 20000 2 2 Mary 20 25000 3 3 Bob 22 27000 4 4 Kathy 30 30000 5 5 Shideh 4 4000 1 SS# Name Age Salary dno 4 Kathy 30 30000 5 Header
Background image of page 7

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

View Full DocumentRight Arrow Icon
09/27/09 8 TERMINOLOGY An exact match selection predicate: б Salary=30,000 (Employee) , б FirstName=“Shideh” (Employee) A range selection predicate: б Salary>30,000 (Employee) , б Salary<30,000 (Employee), б Salary>30,000 and Salary < 32,000 (Employee)
Background image of page 8
09/27/09 9 INTRODUCTION (Cont…) Motivation : Speed-up those queries that reference only a small portion of the records in a file. Analogy : Catalog cards in the library (more than one index). Evaluation : 1. Access time (find) 2. Insertion time (find + add) 3. Deletion time (find + delete) 4. Space overhead Search-key : The attribute (or set of attributes) used to lookup records in a file Primary index : The index whose search key specifies the sequential order of the records within a file. Secondary index : The index whose search key does not specify the sequential order of the records within a file.
Background image of page 9

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

View Full DocumentRight Arrow Icon
09/27/09 10 Indexing Mapping of records to a finite space using a function (computation) E.g., http://www.realmagic.net/dp/1-1.htm
Background image of page 10
09/27/09 11 K F(K) 10 10-1=9 11 11-2=9 12 12-3=9 13 13-4=9 14 14-5=9 15 15-6=9 16 16-7=9 17 17-8=9 18 18-9=9 19 19-10=9 20 20-2=18 21 21-3=18 22 22-4=18 29 29-11=18 30 30-3=27 31 31-4=27 K F(K) 40 40-4=36 ..
Background image of page 11

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

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

This note was uploaded on 01/11/2009 for the course CSCI 485 taught by Professor Ghandeharizadeh during the Fall '08 term at USC.

Page1 / 59

7Index - INDEXING (CHAPTER 12) 1 01/11/09 TOPICS Basic...

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

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