Exercise 20.8 For each of the following queries, identify one possible reason why an optim...

Exercise 20.8 For each of the following queries, identify one possible reason why an optimizer might not find a good plan. Rewrite the query so that a good plan is likely to be found. Any available indexes or known constraints are listed before each query; assume that the relation schemas are consistent with the attributes referred to in the query.

1. An index is available on the age attribute:

   SELECT E.dnoFROM Employee EWHERE E.age=20 OR E.age=10

2. A B+ tree index is available on the age attribute:

   SELECT E.dnoFROM Employee EWHERE E.age<20 AND E.age>10

3. An index is available on the age attribute:

   SELECT E.dnoFROM Employee EWHERE 2*E.age<20

4. No index is available:

   SELECT DISTINCT *FROM Employee E

5. No index is available:

   SELECT AVG (E.sal)FROM Employee EGROUP BY E.dnoHAVING E.dno=22

6. The sid in Reserves is a foreign key that refers to Sailors:

   SELECT S.sidFROM Sailors S, Reserves RWHERE S.sid=R.sid