1 / 12

CS3223 Tutorial 8

CS3223 Tutorial 8. Fan Qi fan.qi@nus.edu.sg Database Lab 1, com1 #01-08. Question 1.1. Emp ( eid , sal , age, did) 20 byte/tuple 20k tuple Dept ( did, projid , budget, status) 40 byte/tuple 5k tuple Proj ( projid , code, report) 2k byte/tuple 1k tuple

nigel
Download Presentation

CS3223 Tutorial 8

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 8 Fan Qi fan.qi@nus.edu.sg Database Lab 1, com1 #01-08

  2. Question 1.1 • Emp (eid, sal, age, did) 20 byte/tuple 20k tuple • Dept(did, projid, budget, status) 40 byte/tuple 5k tuple • Proj (projid, code, report) 2k byte/tuple 1k tuple • MEMORY: 4K byte/page 12 pages What can we infer? • pages • pages • pages

  3. Question 1.1 • Emp (eid, sal, age, did) 20 byte/tuple 20k tuple • Dept(did, projid, budget, status) 40 byte/tuple 5k tuple • Proj (projid, code, report) 2k byte/tuple 1k tuple • MEMORY: 4K byte/page 12 pages • pages • pages • pages Q1: Find all employees with age = 30 Q2: Find all projects with code = 20 Which table should build Clustered B+ tree ? How to estimate the cost of B+ tree

  4. Question 1.1 • Estimate the property of B+ tree • Take example of Emp (eid, sal, age, did) • B+ tree on “age” attribute • Height of tree: • fan out is at least 200 • Clustered: • Leaf page: 100 page • 1 internal level • Unclustered: • Leaf page: • 1 internal level 1 page can hold 200 tuples Date entries in unclustered B+ tree is more compact

  5. Question 1.1 • Estimate the cost of B+ tree • Still on Emp (eid, sal, age, did) • B+ tree on “age” attribute • Suppose the matching tuples is , format 2 data entry is of size • Height of tree: • Clustered: 1 • Unclustered : 1 • Cost of Clustered: • Page capacity : • Leaf page scanned: • RID look up: 0 • Cost of Unclustered: • Page capacity: • Leaf page scanned : • RID look ups:

  6. Question 1.2 (a) • All plans: • P1. Block nested loop join with Emp as outer relation. • P2. Block nested loop join with Emp as inner relation. • P3. Index nested-looop join with Emp as inner relation. • P4. Sort merge join with Emp as outer relation. • P5. Sort merge join with Emp as inner relation. • P6. Hash join with Emp as build relation. • P7. Hash join with Emp as probe relation. Cost(P1) ≥ Cost(P2) Cost(P4) ≥ Cost(P5) Cost(P6) ≥ Cost(P7)

  7. Question 1.2 (a) • Cost(P1) ≥ Cost(P2) ,Cost(P4) ≥ Cost(P5) ,Cost(P6) ≥ Cost(P7) • Only need to compare Cost(P3) , Cost(P2), Cost(P5), Cost (P7) • P3: • P2: • P5: Initial Sorted run of Emp 9, initial Sorted run of Dep 5 after phase 1 of Dep, start merge on the fly • Cost: • P7: each partition is 5 page, no overflow • Cost:

  8. Question 1.2 (b) • All plans: • P1. Block nested loop join with Emp as outer relation. • P2. Block nested loop join with Emp as inner relation. • P3. Index nested-looop join with Emp as inner relation. • P4. Sort merge join with Emp as outer relation. • P5. Sort merge join with Emp as inner relation. • P6. Hash join with Emp as build relation. • P7. Hash join with Emp as probe relation.

  9. Question 1.2 (c) • All plans: • P1. Block nested loop join with Emp as outer relation. • P2. Block nested loop join with Emp as inner relation. • P3. Index nested-looop join with Emp as inner relation. • P4. Sort merge join with Emp as outer relation. • P5. Sort merge join with Emp as inner relation.

  10. Question 3 Reduction factor: E.sal= D.Budget = • Three Table Join Query • Query plans: • {E} , {D}, {P} • {E,D}, {D,P} • {E,D,P} • For each sub query, there are multi plans E P P D D E

  11. Question 3 • Plans for {E} • P1: Table scan. Cost(P1) = 100 • P2: Index scan on E.sal : Matching Tuple = =1, Cost(P2) = 2 • Plans for {D,P} • P3: Block nested loop join D as outer relation. • Cost: • P4: Index nested loop join with Das outer relation. • Cost: • …

  12. Thank you! See you next week!

More Related