As usual, the answer is "it depends".
Version 11 SQL Anywhere servers support index-only retrieval; if all of the attributes required in the computation are covered by the index, and an indexed retrieval is estimated to be less costly than other access paths, then the server will use the index (and solely the index) to retrieve the necessary values to compute the result.
Both queries above have the potential to be satisfied by index-only retrieval - it doesn't matter that the first query references an attribute in the SELECT list, and the second uses the MAX aggregate function.
The tradeoff to adding a column to an existing index is whether or not your workload as a whole costs less to execute. Adding a column may make index maintenance more expensive, not only because the index is larger but also because your application may update the newly-added column more often than other columns that are already indexed.
SQL Anywhere's index consultant can determine for you whether or not an index can be advantageous with your specific workload.
answered
19 Jan '10, 21:34
Glenn Paulley
10.8k●5●76●106
accept rate:
43%