View the step-by-step solution to:

# Consider a new cost model that is DIFFERENT from what we have in the slides:

Consider a new cost model that is DIFFERENT from what we have in the slides:

Cost(P1 P2)=Cost(P1)+Cost(P2)+Size(intermediate result of P1)+Size(intermediate result of P2) The intermediate result is dened as follows:

Furthermore, assume that the cost of a scan is 0 Using this cost model, we would like to estimate Cost((A B) (C D)). Which of the expressions given below can be used to calculate Cost((A B) (C D))? Note that the parts of the expression amounting to zero should be eliminated.

A)    1⁄3 Size(A)+1⁄3 Size(B)+1⁄3 Size(C)+1⁄3 Size(D)+Size(A B)+Size(C D)+Cost(D)+Cost(A)+Cost(B)+Cost(C)

B)    =1⁄3 Size(A)+1⁄3 Size(B)+1⁄3 Size(C)+1⁄3 Size(D)+Size(A B)+Size(C D)

C)    Cost(A B)+Cost(C D)+Size(A B)+Size(C D)

Assume that the size after joining R1 and R2 is B(R1 R2)=B(R1) B(R2) 0.01 and we want to join ve tables A,B,C,D,E, using a left- deep tree in the order of A B C D E. The sizes of them are B(A)=900,B(B)=300,B(C)=800,B(D)=400,B(E)=100. What will be the minimal required memory we must have if we allow materializing the intermediate results? Assume that we use simple hash- join, which means that, for joining two tables (they could be the original tables or intermediate results), we always have one of them hashed and stored entirely in memory, and then we read from the other table one block at a time to join with the hashed table in memory.Notice that order of siblings in the same subtree does not matter, meaning that A

B C D Eis equivalent as B A C D E, in a left-deep tree.

a)   1000

b)   900

c)    800

d)   2500

Suppose that we want to build an index for a relation R on a key K . Also, suppose that the number of records in R is 2^10 , the size of a record is 2 bytes, the size of a key-pointer pair is 4 bytes, and the size of a data block is 2^3 bytes. Assuming that the index is dense and unclustered, what is the minimum possible number of blocks needed to store the index.

a)    2^8

b)   2^7

c)    2^5

d)   2^9

Consider a new cost model that is DIFFERENT from what we have in the slides: Cost(P1⋈P2) = Cost(P1) + Cost(P2) + Size(intermediate result of P1) + Size(intermediate result of P2) The intermediate result is defined as follows:

Furthermore, assume that the cost of a scan is 0 Using this cost model, we would like to estimate Cost((AB)C))D). Which of the expressions given below can be used to

calculate Cost((AB)C))D)? Note that the parts of the expression amounting to zero should be eliminated.

A)  = 1⁄2 Size(C) + Cost(AB) + Size(AB)C)) + 1⁄2 Size(D)

B)  =1⁄2 Size(A) + 1⁄2 Size(B) + 1⁄2 Size(C) + Size(AB) + Size(AB)C)) + 1⁄2 Size(D) +Cost(D) + Cost(A)

C)  = 1⁄2 Size(A) + 1⁄2 Size(B) + 1⁄2 Size(C) + Size(AB) + Size(AB)C)) + 1⁄2 Size(D)

Assume that the size after joining R1 and R2 is B(R1R2)=B(R1)B(R2)0.01 and we want to join ve tables A,B,C,D,E, using a left-deep tree in the order of ABCDE. The sizes of them

are B(A)=200,B(B)=100,B(C)=500,B(D)=500,B(E)=300. What will be the minimal required memory if we want to pipeline (the output from previous step will feed directly as the input to the next step) all the intermediate steps? Assume that we use simple hash-join, which means that, for joining two tables (they could be the original tables or intermediate results), we always have one of them hashed and stored entirely in memory, and then we read from the other table one block at a time to join with the hashed table in memory. Notice that order of siblings in the same subtree does not matter, meaning that ABCDE is equivalent as BACDE, in a left-deep tree.

A)  1600

B)  1100

C)  1400

D)  500

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents