1 / 14

CS3223 Tutorial 4

CS3223 Tutorial 4. Fan Qi fan.qi@nus.edu.sg Database Lab 1, com1 #01-08. Block I/O Sorting. Key Idea: R/W multi page per I/O Cost: Number of Passes:. 1 block for output. Selection. Access: Table Scan Index Scan Index Seek Index Intersection Index Access:

galvin-shepherd
Download Presentation

CS3223 Tutorial 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS3223 Tutorial 4 Fan Qi fan.qi@nus.edu.sg Database Lab 1, com1 #01-08

  2. Block I/O Sorting • Key Idea: • R/W multi page per I/O • Cost: • Number of Passes: 1 block for output

  3. Selection • Access: • Table Scan • Index Scan • Index Seek • Index Intersection • Index Access: • B+ Tree : range and equality • Hash : equality only • Cost • B+ Tree • Navigation cost • Leaf page scan cost • RID look up cost • Hash Index • Chain Cost (Reading Bucket Page into Memory) • RID look up cost

  4. Question 1 Sequential I/O: • Pass 0: • How may sorted runs created ? • How large is each sorted runs ? • pages • What is the I/O cost for computing each sorted run? • It is sequential reading / writing, why? • Therefore, total cost is: Random I/O:

  5. Question 1 Sequential I/O: • 319-way merge: • How many passes needed to sort 31,250 sorted run? • How many I/O per pass ? • What is the total I/O cost? • , why not sequential? Random I/O: Disk Memory 1 321 641 961 in 2 322 642 962 … … … … in in out 320 640 960 1280 in

  6. Question 1 Sequential I/O: Random I/O: • (iv) 8 32-page input buffer, 1 64-page output buffer • How many passes? • or ? • How many I/O per pass? • How many blocks read ? • Read Cost: • ms • How many blocks write? • Write Cost: • ms, why 79? • Total cost ?

  7. Question 1 • Take-away points • Block I/O reduces sorting time • Diminishing return at some point, why? • In practice, read/write block are of the same size

  8. Question 3 Total Data Record: 1. Table Scan pages 2. Index Scan pages 3. Index Seek 4. Index Seek 5. Index Intersec. 6. Index seek 7. Index seek

  9. Question 3 • Index Seek pr 250 pages Rid Look up to get name Disk

  10. Question 3 • Index Intersection . r p Intersect 250 pages 500 pages Rid Look up to get name Disk

  11. Question 3 • Index seek p 200 pages Rid Look up to get name for each record Disk

  12. Question 4 • A) • B+ Tree • Clustered , Un-clustered? • Matching Records? • Cost? • Table Scan • Cost? • 500 pages

  13. Question 4 • B) • Hash Index • Matching Records? • Cost? • Table Scan • Cost? • 500 pages

  14. Thank you! Happy Recess Week!

More Related