1 / 13

Example Join-- File Information

Example Join-- File Information. Emp( Fn Char(10), Minit Char, LN Char(20), SSN number(9), Bdate Date, Addr char(40), Sex Char, Salary Number(9,2) , Dno number(3)). Employee Numbers. r = 10,000 records |r| = 99 bytes/record Block Size = 512 bytes/block bf = 5 records/block

erol
Download Presentation

Example Join-- File Information

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. Example Join-- File Information • Emp(Fn Char(10), • Minit Char, • LN Char(20), • SSN number(9), • Bdate Date, • Addr char(40), • Sex Char, • Salary Number(9,2) , • Dno number(3))

  2. Employee Numbers • r = 10,000 records • |r| = 99 bytes/record • Block Size = 512 bytes/block • bf = 5 records/block • b = 2,000 blocks • B+-Tree on Dno: • Ldno = 3, ddno = 125, sdno= 80, mdno = 47 • BLdno = 434

  3. Example Join-- File Information • Dept(D# Number(3), • Dname Char(20), • MGRSSN Number(9), • MgrStartDate Date)

  4. Dept Numbers • r = 125 records • |r| = 38 bytes/record • bf = 13 records/block • b = 10 blocks • B+-Tree on D#: • Ldno = 2, ddno = 125, sdno= 1, mdno = 47 • BLdno = 5

  5. The Sample Query: • I want to use Emp ê Dept. What are my options? Dno=D# • Nested Loop approach. • Use B-Tree on Emp.Dno. • Use B-Tree on Dept.D# • Cluster Employee and Department together.

  6. Join Query -- Nested Loop Emp ê Dept. Dno=D# • CJ1 = bdept + bEmp * bDept = 10 + 2000 * 10 = 20,010

  7. Join Query -- B-Tree on D# Emp ê Dept. Dno=D# • CJ2D# = bEmp + rEmp * (LDept.D# + 1) = 2000 + 10000 * (2 + 1) = 32,000

  8. Join Query -- B-Tree on Dno Emp ê Dept. Dno=D# • CBnDno = L+ (és / (ém/2 ù -1) ù - 1) + s = 3 + (é80 / (é47/2 ù -1) ù - 1) + 80 = 3 + 4 - 1 + 80 = 86 • CJ2D# = bDept + rDept * (CBnDno) = 10 + 125 * (86) = 10,760

  9. Join Query – B-Tree on Dno Disucssion • This is the better choice. • Note: I have ignored the WTDC here because it would be the same in all cases.

  10. Join Query -- Clustered Files Emp ê Dept. Dno=D# • CJ3 = bR + bs = 2000 + 10 = 2,010 • This is the best choice. • Note: it does restrict how the files are stored.

  11. Join Query – Sort Files First Emp ê Dept. Dno=D# • Sort Emp: bR*Log2(bR) = 2,000*11= 22000 • Sort Dept: bS*Log2(bS) = 10*4 = 40 • CJ3 = bR + bS = 2000+10 = 2010 = = 24050

  12. Join Query – Build Hash On Emp.DNO Emp ê Dept. Dno=D# • Build Hash: bR + rR = 2000+10000 = 12000 • Hash Join: = 1+ s = 81 • CJ3 = bs + rS*(81) = 10+125*(81) = 10135 = = 22135

  13. Example WTDC • If all columns are needed, we have • |r| = 99+38 -3 (D# stored only once) = 134 • r = rEmp = 10,000 • Bf = floor(512/134) = 3 • b = ceil(10,000/3) = 3334 • Thus the WTDC = 3334

More Related