QUESTIONS:
select * from EMP where EMP.salary 40000 AND EMP.age 30
Assume that there is a clustered B+ tree index () on EMP.salary
and an unclustered B+ tree index () on EMP.age. The employee
salaries range from 20001 till 100000 and the ages range from 21 till
60. The statistics are as follows:
NCARD = 100000 tuples, TCARD = 1000 pages, P = 0.8,
ICARD() = 1000 values,
NINDX() = 500 pages, ICARD() = 40 values, NINDX() = 200 pages.
Assume that we are concerned only with the IO cost and that the buffer pool allocated
to this query can hold 1200 pages.
select distinct (S.name, S.hostel) from STUDENT S
where the STUDENT relation has the schema [SRNo, name, hostel, gpa] and individual -tree indexes are available on the SRNo and name attributes, respectively.
Assume that you use, for this estimation, the strategy outlined in Figure 1 of the Sampling paper discussed in class. Within this framework, describe your: (a) partitioning approach, (b) random sampling strategy, and (c) computation technique for the RandomSample() function.
[Hint: Consider the possibility of result partitions with fractional sizes.] (6 marks)
[Note: Assume plan cost monotonicity (PCM) is strictly adhered to by all plan cost functions.] (4 marks)
|
|