Chapter 19Cost-based Query OptimizationConsider 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. FIGURE 1: COLUMN INFORMATION FIGURE 2: TABLE INFORMATION 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…!Table_nameColumn_nameNum_distinctDEPARTMENTDnumber50DEPARTMENTMgr_ssn50EMPLOYEESsn10000EMPLOYEEDno50EMPLOYEESalary500Table_nameNum_rowsBlocksDEPARTMENT505EMPLOYEE100002000Table_nameColumn_indexIndex_typeUniqueBlevel (i.e. x-value)DEPARTMENTDnumberPRIMARYYES1DEPARTMENTMgr_ssnSECONDARYYES1EMPLOYEESsnPRIMARYYES1EMPLOYEEDnoSECONDARYNO1EMPLOYEESalarySECONDARYNO1CS-6360UT-DallasDr. Chris Irwin DavisChapter 19Cost-based Query OptimizationCost-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-6360UT-DallasDr. Chris Irwin