Chapter 19 Cost based Query Optimization Consider the following join which is given in your textbook 19 5 2 p 720 OP7 DEPARTMENT Mgr ssn Ssn EMPLOYEE Calculate the cost based estimation of executing this query using a DEPARTMENT as the outer loop and then b EMPLOYEE as the outer loop using textbook single loop join algorithms i e J2 Note The join formulas from the textbook pp 718 19 will be provided during the final exam Assume that the Column Table and Index meta data are the values in Figures 1 3 Table name DEPARTMENT DEPARTMENT EMPLOYEE EMPLOYEE EMPLOYEE Column name Dnumber Mgr ssn Ssn Dno Salary Num distinct 50 50 10000 50 500 FIGURE 1 COLUMN INFORMATION Table name DEPARTMENT EMPLOYEE Num rows 50 10000 Blocks 5 2000 FIGURE 2 TABLE INFORMATION Table name DEPARTMENT DEPARTMENT EMPLOYEE EMPLOYEE EMPLOYEE Column index Dnumber Mgr ssn Ssn Dno Salary Index type PRIMARY SECONDARY PRIMARY SECONDARY SECONDARY Unique YES YES YES NO NO Blevel i e x value 1 1 1 1 1 FIGURE 3 INDEX INFORMATION To begin we must calculate the blocking factor of the join bfrDEP EMP To do this we need to first calculate the blocking factor of the individual tables bfrDEP and bfrEMP The blocking factor of the DEPARTMENT table is the number of rows records per block bfrDEP 50 5 10 The blocking factor of the EMPLOYEE table is the number of rows records per block bfrDEP 10000 2000 5 The blocking factor of the joined table is the number of joined rows records per block bfrDEP EMP 1 1 bfrDEP 1 bfrDEP bfrDEP EMP 1 1 10 1 5 bfrDEP EMP 1 3 10 bfrDEP EMP 10 3 bfrDEP EMP 3 Now we can calculate the costs of the two different J2 single loop join operations CS 6360 UT Dallas Dr Chris Irwin Davis Chapter 19 Cost based Query Optimization Cost based estimations DEPARTMENT as the outer loop If DEPARTMENT is the outer loop then the index attribute of the inner loop is EMPLOYEE Ssn which is a primary key index Therefore we use join formula J2c to estimate the cost CostJ2c bDEP rDEP xSsn 1 jsOP6 rDEP rEMP bfrDEP EMP CostJ2c 5 50 1 1 1 10000 50 10000 3 CostJ2c 5 100 50 3 CostJ2c 122 EMPLOYEE as the outer loop If EMPLOYEE is the outer loop then the index attribute of the inner loop is DEPARTMENT Mgr ssn which is a unique secondary index Therefore we use join formula J2a to estimate the cost The selection cardinality sMgr ssn of DEPARTMENT Mgr ssn is 1 because there is exactly one department manager per department CostJ2a bEMP rEMP xMgr ssn 1 sMgr ssn jsOP6 rDEP rEMP bfrDEP EMP CostJ2a 2000 10000 1 1 1 1 10000 50 10000 3 CostJ2a 2000 30000 50 3 CostJ2a 32017 CS 6360 UT Dallas Dr Chris Irwin Davis