Asst$4$ QC$($i$)$

SELECT MemberID, DaysWorked

FROM WorksOn

WHERE ActivityID $=316$;

Explain why the query can't use the primary index, and write SQL to CREATE an index that that will allow much faster execution of the query.

Why the query can't use the primary index: The table structure $($when no INDEX is explicitly created$)$ is clustered by the primary key, which is $($MemberID$,$ ActivityID$).$ This uses lexicographic order, that is$,$ rows with the same MemberID are together, and within those for a given MemberID, they are sorted by ActivityID. So the rows with ActivityID $=316($which have varying MemberID values$)$ will be scattered among the rows of the table, and can't be found in any simple way except by looking at all the rows.

Possible index creation to speed this query

CREAIE INDEX idx$\_$activityid ON WorksUn $($ActivityID$)$;

Other possibilities include:

$-$ change the clustering, so there is a clustering index on $($ActivityID$,$ MemberID$)-$ this is also a suitable primary key, just in the reverse order of columns

$-$ Introduce a covering index on $($ActivityID$,$ MemberID, DaysWorked$)$

SELECT $*$

FROM WorksOn

WHERE DaysWorked $>=7$ AND DaysWorked $<mtext></mtext><mn>2</mn><mn>1</mn>$;

Can this query be processed effectively with a hash$-$based unclustered index on DaysWorked? Justify your answer.

Since the query asks for a range of values of DaysWorked, the hash index would not be suitable because it does not store data in a sorted order $($only index entries for each value of DaysWorked separately, are kept together in an index bucket$).$ So the index entries for each value of DaysWorked in this range are in a different place in the index. For range queries like the one provided, a tree$-$based index is appropriate.