Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Consider a relation with the following schema: Executives (id: integer, name string, title:string, level: integer) The Executives relation consists of 100,000 tuples stored in disk
Consider a relation with the following schema: Executives (id: integer, name string, title:string, level: integer) The Executives relation consists of 100,000 tuples stored in disk pages. The relation is stored as simple heap file and each page stores 100 tuples. There are 10 distinct titles in the Executives hierarchy and 20 distinct levels ranging from 0-20 Suppose that the following SQL query is executed frequently using the given relation: SELECT E.ename FROM Executives WHERE Etitle"CEO and E.level > 15 Your job is to analyze the query plans given below and estimate the cost of the best plan utilizing the information given about different indexes in each part. a) Compute the estimated result size and the reduction factor (selectivity) of this query (1 mark) b) Compute the estimated cost of the best plan assuming that a clustered B+ tree index on (title, level) is (the only index) available. Suppose there are 200 index pages, and the index uses Alternative 2. Discuss and calculate alternative plans. (1 mark) c) Compute the estimated cost of the best plan assuming that an unclustered B+ tree index on (level) is (the only index) available. Suppose there are 200 index pages, and the index uses Alternative 2. Discuss and calculate alternative plans. (1 mark) d) Compute the estimated cost of the best plan assuming that an unclustered Hash index on (title) is (the only index) available. The index uses Alternative 2. Discuss and calculate alternative plans. (1 mark) e) Compute the estimated cost of the best plan assuming that an unclustered Hash index on (level) is (the only index) available. The index uses Alternative 2. Discuss and calculate alternative plans. (1 mark) Consider a relation with the following schema: Executives (id: integer, name string, title:string, level: integer) The Executives relation consists of 100,000 tuples stored in disk pages. The relation is stored as simple heap file and each page stores 100 tuples. There are 10 distinct titles in the Executives hierarchy and 20 distinct levels ranging from 0-20 Suppose that the following SQL query is executed frequently using the given relation: SELECT E.ename FROM Executives WHERE Etitle"CEO and E.level > 15 Your job is to analyze the query plans given below and estimate the cost of the best plan utilizing the information given about different indexes in each part. a) Compute the estimated result size and the reduction factor (selectivity) of this query (1 mark) b) Compute the estimated cost of the best plan assuming that a clustered B+ tree index on (title, level) is (the only index) available. Suppose there are 200 index pages, and the index uses Alternative 2. Discuss and calculate alternative plans. (1 mark) c) Compute the estimated cost of the best plan assuming that an unclustered B+ tree index on (level) is (the only index) available. Suppose there are 200 index pages, and the index uses Alternative 2. Discuss and calculate alternative plans. (1 mark) d) Compute the estimated cost of the best plan assuming that an unclustered Hash index on (title) is (the only index) available. The index uses Alternative 2. Discuss and calculate alternative plans. (1 mark) e) Compute the estimated cost of the best plan assuming that an unclustered Hash index on (level) is (the only index) available. The index uses Alternative 2. Discuss and calculate alternative plans. (1 mark)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started