SQL Processing - Relational Database Index Design and the Optimizers - page 44

ž There is a sequence requirement but the following conditions are both true:
ž There is an index that is able to provide the result rows in the ORDER
BY sequence, for example (CNO, IDATE DESC).
ž The optimizer decides to use this index in the traditional way, that is,
the first qualifying index row and its table row are accessed, followed
by the second qualifying index row and its table row, and so on.
If the optimizer assumes that the program will fetch the whole result set ,itmay
select the wrong access path—perhaps a full table scan.

Alternative 2: Early Materialization

By far the most common reason for this choice is that a sort of the result rows is
necessary; this will be reported by EXPLAIN or its equivalent. There are a few
special cases, partly DBMS specific, in which the whole result is materialized
even though the result rows are not sorted. These cases may also be detected by
the EXPLAIN output.
In our example, the DBMS would have to sort the result rows if there isn't an
index starting with either the columns CNO and IDATE or with just the column
IDATE. If the DBMS cannot read an index backwards, the index column IDATE
would have to be specified as descending: IDATE DESC.
If early materialization is chosen, some DBMSs materialize the result table
at OPEN CURSOR, others at the first FETCH. It is only necessary to know this
if reading an SQL trace. Otherwise it does not make a significant difference; it
seems unlikely that a program would issue an OPEN CURSOR without issuing
any FETCH calls to it.

What Every Database Designer Should Remember

Sorting results rows implies that the whole result table is materialized even if
only the first result row is fetched.

EXERCISES

3.1. Design the best possible indexing for the following query:

SQL 3.7

SELECT LNAME, FNAME, CNO

FROM CUST

WHERE SEX = 'M'

AND
HEIGHT > 190

ORDER BY LNAME, FNAME