How many sorted runs are produced in the first pass

Assignment Help Basic Computer Science

Reference no: EM131210883

Consider processing the following SQL projection query:

SELECT DISTINCT E.title, E.ename FROM Executives E

You are given the following information:

Executives has attributes ename, title, dname, and address; all are string fields of the same length.

The ename attribute is a candidate key.

The relation contains 10,000 pages.

There are 10 buffer pages.

Consider the optimized version of the sorting-based projection algorithm: The initial sorting pass reads the input relation and creates sorted runs of tuples containing only attributes ename and title. Subsequent merging passes eliminate duplicates while merging the initial runs to obtain a single sorted result (as opposed to doing a separate pass to eliminate duplicates from a sorted result containing duplicates).

1. How many sorted runs are produced in the first pass? What is the average length of these runs? (Assume that memory is utilized well and that any available optimization to increase run size is used.) What is the I/O cost of this sorting pass?

2. How many additional merge passes will be required to compute the final result of the projection query? What is the I/O cost of these additional passes?

3. (a) Suppose that a clustered B+ tree index on title is available. Is this index likely to offer a cheaper alternative to sorting? Would your answer change if the index were unclustered? Would your answer change if the index were a hash index?

(b) Suppose that a clustered B+ tree index on ename is available. Is this index likely to offer a cheaper alternative to sorting? Would your answer change if the index were unclustered? Would your answer change if the index were a hash index?

(c) Suppose that a clustered B+ tree index on hename, titlei is available. Is this index likely to offer a cheaper alternative to sorting? Would your answer change if the index were unclustered? Would your answer change if the index were a hash index?

4. Suppose that the query is as follows:
SELECT E.title, E.ename FROM Executives E
That is, you are not required to do duplicate elimination. How would your answers to the previous questions change?

Reference no: EM131210883

Questions Cloud

Conduct a chi-squared goodness-of-fit test : Based on the above information, conduct a Chi-squared Goodness-of-Fit Test at 1% significance level.

Describe the iterator interface and explain its advantages : What role do statistics gathered from the database play in query optimization?

Determine the profit or loss for the month : Expense Statement for the month the above facts and determine the profit or loss for the month - Sales price per unit $100, Labor cost per unit sold $16. We are not including any non-cash expenses.

What will happen to domestic interest rates : If the Fed does not do the open market sale, what will happen to domestic interest rates and the money supply? - In which case is the foreign exchange transaction sterilized?

How many sorted runs are produced in the first pass : How many additional merge passes will be required to compute the final result of the projection query? What is the I/O cost of these additional passes?

Understanding of the sources of interest rate risk : FN0366 Interest Rate, Liquidity and Operational Risks Assignment. For your final evaluation, you are required to present a report critically analysing and demonstrating your understanding of the sources, measurement and management of the three risk..

What is consumer surplus : What is consumer surplus? - Using real-world data, what information would you need to measure consumer surplus for a product?

What is producer surplus : What is producer surplus? - Using real-world data, what information would you need to measure producer surplus for a product?

Create a plan to implement a firm strategy and manage : Describe strategic planning techniques used to formulate alternative strategies designed to achieve stated business goals. Create a plan to implement a firm's strategy and manage the change from current operations.

User Account

All Pages