This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 214 Chapter 20 CREATE VIEW MgrAge (dname, age) AS SELECT D.dname, E.age FROM Emp E, Dept D WHERE D.mgr = E.ename 1. Describe a situation in which the first query is likely to outperform the second query. 2. Describe a situation in which the second query is likely to outperform the first query. 3. Can you construct an equivalent query that is likely to beat both these queries when every employee who earns more than $100,000 is either 35 or 40 years old? Explain brieﬂy. Answer 20.9 1. Consider the case when there are very few or no employees having salary more than 100K. Then in the first query the nested part would not be computed (due to short circuit evaluation) whereas in the second query the join of Emp and MgrAge would be computed irrespective of the number of Employees with sal > 100K. Also, if there is an index on dname , then the nested portion of of the first query will be eﬃcient. However, the index does not affect the view in the second query since it is used from a view. 2. In the case when there are a large number of employees with sal > 100K and the Dept relation is large, in the first query the join of Dept and Emp would...
View Full Document
This note was uploaded on 01/17/2012 for the course EGN 4302 taught by Professor Dr.vishak during the Fall '12 term at University of Central Florida.
- Fall '12