Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 249

tolerable compromise may be once again fat or even ideal indexes, to make the
materialization of the whole result table fast enough.
Unnecessary Table Touches
A recently published topic (1) by Gulutzan and Pelzer has an interesting approach:
It describes many commonly used SQL tuning tricks and then tries them out with
eight different DBMSs. The trick is considered successful if the improvement in
local response time is more than 5%.
A fat index ( covering index ) is, of course, one of the tricks. Surprisingly the
result is only 6/8; two of the eight products did not choose an index only access
path. The authors then warn about the following restriction related to index only:
Ordinarily, using a covering index will save one disk read (GAIN 6/8). The gain
is automatic but only if the columns in the select list exactly match the columns
in the covering index. You lose the gain if you select functions or literals, or you
put the column list in a different order.
...
SELECT name FROM Table1
ORDER BY name
Will the DBMS do the “smart” thing and scan the covering index, instead of
scanning the table and then sorting. Perhaps. But Cloudscape won't use
covering indexes unless it needs them for the WHERE-clause anyway, and
Oracle won't use an index if the result might involve Nulls. So there is a gain if
you assume that NULL names don't matter and you change the search to
SELECT name
FROM Table1
WHERE name > ''
ORDER BY name
GAIN 7/8
Literals in SELECT lists are a well-known pitfall, as well as the NULL
issue with Oracle, but the claim about the order of columns in the SELECT list
is astonishing. The following sentence in the summary of the quoted paragraph
is even more surprising:
DBMSs never use covering indexes when there are joins or groupings.
The claim about joins excluding index only does not agree with our experience
with DB2, Oracle, and SQL Server, nor with the recommendations in Chapter 8.
It is possible that some products do have such a strange limitation, but it could
also be a misinterpretation of the EXPLAIN. If the EXPLAIN does not show
the expected access path, it is easy to jump to the conclusion that the optimizer
did not see the best alternative. It is possible, and is not uncommon, that the
optimizer did in fact see the “best” alternative, but the estimate differed so much
from the actual value, that the optimizer chose the wrong access path simply