Assume that there are separate B+
-tree indexes on the attributes clientNo and maxRent of
Client and that the optimizer supports both sort–merge join and block nested loop join. In
considering all possible ways to access the Client relation, we would calculate the cost of a
linear search of the relation and the cost of using the two B+
-trees. If the optimal strategy
came from the B+
-tree index on maxRent, we would then discard the other two methods.
However, use of the B+
-tree index on clientNo would result in the Client relation being sorted
on the join attribute clientNo, which would result in a lower cost for a sort–merge join of
Client and Viewing (as one of the relations is already sorted)