Consider the following relation: Recipe (rid: int, name: varchar(40), time: int,
ID: 3809503 • Letter: C
Question
Consider the following relation:
Recipe (rid: int, name: varchar(40), time: int, difficulty: int)
Assume the presence of a clustered index idx1 on rid and an unclustered index idx2 on time.
A. Give an example of an update query that would be significantly speeded up by idx1
B. Give an example of an update query that would be significantly speeded up by idx2
C. Give an example of an update query that would be significantly slowed down by idx1 or idx2
D. Give an example of an update query that is not impacted by idx1 or idx2
Explanation / Answer
Clustered Index idx1 d,
All rows are sorted by the rid(recipe id)
Exa.
r1 curry 30 1
r2 chapati 15 2
r3
r4
UnClustered index idx2 on time table
Index table:
Time Row_Locator(on eid as clustered index in also on this table)
15 r2
20 r1
30
40
A) sppedup by idx1
update table Recipe SET name = "Biryani" Where rid <10 AND rid >=8
As here the table is sorted based on rid, the lookup will be needing just the first value less than 10 and the first value greater or equal to 8. And direct update on those records.
B)sppedup by idx2.
Update table Recipe SET name = "Bread" Where time > 15
Here the index table has the entries sorted by time so the just get the row locator whihc is rid greater than 15 and perform update.
c) slowed down by idx1 or idx2
update table Recipe SET rid = 5, time = 15 Where name = "Bread"
As update is required in the clustered index to reorder the clustered index and the index table of unclustered index idx2 also and later the update of the record in table
d)not impacted by idx1 or idx2
update table Recipe Dificulty = 30 where name like '%Bread'
as this query doesn't contain the clustered and unclustered index, the retrival will not afftect by any kind of indexing in table.