05_btree

05_btree - CPS216: Data-intensive Computing Systems...

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

View Full Document Right Arrow Icon
1 CPS216: Data-intensive Computing Systems Operators for Data Access Shivnath Babu
Background image of page 1

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

View Full DocumentRight Arrow Icon
Problem Relation: Employee (ID, Name, Dept, …) 10 M tuples (Filter) Query: SELECT * FROM Employee WHERE Name = “Bob”
Background image of page 2
3 Solution #1: Full Table Scan Storage: Employee relation stored in contiguous blocks Query plan: Scan the entire relation, output tuples with Name = “Bob” Cost: Size of each record = 100 bytes Size of relation = 10 M x 100 = 1 GB Time @ 20 MB/s 1 Minute
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Solution #2 Storage: Employee relation sorted on Name attribute Query plan: Binary search
Background image of page 4
5 Solution #2 Cost: Size of a block: 1024 bytes Number of records per block: 1024 / 100 = 10 Total number of blocks: 10 M / 10 = 1 M Blocks accessed by binary search: 20 Total time: 20 ms x 20 = 400 ms
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 Solution #2: Issues Filters on different attributes: SELECT * FROM Employee WHERE Dept = “Sales” Inserts and Deletes
Background image of page 6
7 Indexes Data structures that efficiently evaluate a class of filter predicates over a relation Class of filter predicates: Single or multi-attributes ( index-key attributes ) Range and/or equality predicates (Usually) independent of physical storage of relation: Multiple indexes per relation
Background image of page 7

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

View Full DocumentRight Arrow Icon
8 Indexes Disk resident Large to fit in memory Persistent Updated when indexed relation updated Relation updates costlier Query cheaper
Background image of page 8
Relation: Employee (ID, Name, Dept, …) (Filter) Query: SELECT
Background image of page 9

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

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

Page1 / 50

05_btree - CPS216: Data-intensive Computing Systems...

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

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