lecture13 - Query Processing Fundamentals of Database...

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

View Full Document Right Arrow Icon
Query Processing Fundamentals of Database Systems: ch 15 Database System Concepts : ch 13
Background image of page 1

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

View Full DocumentRight Arrow Icon
Overview User select * from R, S where R, B+Tree on R.a S, Hash Index on S.a Results Query Parser Resolve the references, Syntax errors etc. Converts the query to an internal format relational algebra like Query Optimizer Find the best way to evaluate the query Which index to use ? What join method to use ? Query Processor Read the data from the files Do the query processing joins, selections, aggregates
Background image of page 2
Cost z Complicated to compute z We will focus on disk: z Number of I/Os ? z Not sufficient z Number of seeks matters a lot… why ? z t T – time to transfer one block z t S – time for one seek z Cost for b block transfers plus S seeks b * t T + S * t S z Measured in seconds
Background image of page 3

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

View Full DocumentRight Arrow Icon
Selection Operation z select * from person where SSN = “123” z Option 1: Sequential Scan z Read the relation start to end and look for “123” z Cost: suppose person blocks are allocated contiguously z Let b r = Number of relation blocks z Then: ± 1 seek and br block transfers z If person blocks are NOT allocated contiguously ± b r * (t S + t T )sec z Improvements: ± If SSN is a key, then can stop when found ± So on average, b r / 2 blocks accessed
Background image of page 4
Selection Operation z select * from person where SSN = “123” z Option 2 : Binary Search: z Pre-condition: z The relation is sorted on SSN z Selection condition is an equality ± E.g. can’t apply to “ Name like ‘%424%’” z Do binary search z Cost of finding the first tuple that matches ± log 2 ( b r ) * ( t T + t S ) ± All I/Os are random, so need a seek for all
Background image of page 5

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

View Full DocumentRight Arrow Icon
Selection Operation z select * from person where SSN = “123” z Option 3 : Use Index z Pre-condition: z An appropriate index must exist z Use the index z Find the first leaf page that contains the search key z Retrieve all the tuples that match by following the pointers ± If primary index, the relation is sorted by the search key ± Go to the relation and read blocks sequentially ± If secondary index, must follow all points using the index
Background image of page 6
Selection Operation z Selections involving ranges z select * from accounts where balance > 100000 z select * from matches where matchdate between ’10/20/06’ and ’10/30/06’ z Option 1: Sequential scan z Option 2: Using an appropriate index z Can’t use hash indexes for this purpose z Cost formulas: ± Range queries == “equality” on “non-key” attributes
Background image of page 7

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

View Full DocumentRight Arrow Icon
z Complex selections z Conjunctive : select * from accounts where balance > 100000 and SSN = “123” z Disjunctive : select * from accounts where balance > 100000 or SSN = “123” z Option 1: Sequential scan z (Conjunctive only) Option 2: Using an appropriate index on one of the conditions z E.g. Use SSN index to evaluate SSN = “123”. Apply the second condition to the tuples that match z Or do the other way around (if index on balance exists) z Which is better ? z
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 10/25/2009 for the course EE 2011 taught by Professor Denny during the Spring '09 term at National Tsing Hua University, China.

Page1 / 41

lecture13 - Query Processing Fundamentals of Database...

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

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