1. (Part A.) What is meant by the term heuristic optimization? Discuss the main
ID: 3669960 • Letter: 1
Question
1. (Part A.) What is meant by the term heuristic optimization? Discuss the main heuristics that are applied during query optimization.
(Part B.) Consider the following database:
Apply following query by two different methods given below and show the intermediate relations. Also describe which one of the methods is good in query optimization context and why?
Query
SELECT E.Lname, E.Bdate, D.Mgr_ssn
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.Salary >= 30000 AND D.Dname = ‘Research’
AND E.Dno = D.Dnumber;
Method1: Apply Join first and then Selection according to given conditions
Methdo2: Apply Selection first and then Join according to given conditions
EMPLOYEE Mnit Lname Ssn Bdate Address Sex Salary Super ssn Dno name ohn FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TXM 40000 888665555 5 Alicia JenniferS Wallace 9876543211941-06-20 291 Berry, Bellaire, TX Ramesh | K | Narayan | 666884444 1962-09-15 | 975 Fire Oak, Humble, TX | M | 38000 | 333445555 Joyce AhmadV Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 James Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 3334455555 J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F25000 987654321 4 F 43000 888665555 4 A English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 3334455555 DEPARTMENT DEPT LOCATIONS Dnumber Dlocation Houston Stafford Bellaire Sugarland Houston Dnumber Mgr start date 1988-05-22 1995-01-01 1981-06-19 Dname Research Administration Headquarters Mgr ssn 333445555 987654321 888665555 4 5 5 5 4Explanation / Answer
Can help you with this:
In heuristic optimization, heuristics are used to reduce the cost of optimization instead of analyzing the number of different plans to find out the optimal plan.
Example:
A analyzing optimizer would use the rule ‘perform selection operation as early as possible’ without finding out whether the cost is reduced by this transformation.
Heuristics approach usually helps to reduce the cost but not always. The heuristics that are applied during query optimization are :
1.Pushes the selection and projection operations down the query tree
2.Left-deep join trees- convenient for pipelined evaluation
3.Non-left-deep join trees
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.