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

SQL 14.3

SELECT LNAME, FNAME, CNO

FROM CUST

WHERE SEX = 'M'

AND
(WEIGHT > 90 OR HEIGHT > 190)

ORDER BY LNAME, FNAME

SQL 14.4

SELECT LNAME, FNAME, CNO

FROM CUST

WHERE SEX = 'M'

AND
WEIGHT > 90

UNION

SELECT LNAME, FNAME, CNO

FROM CUST

WHERE SEX = 'M'

AND
HEIGHT > 190

ORDER BY LNAME, FNAME

Unnecessary Sort

For many optimizers, some cursors with ORDER BY are too difficult in the sense
that the optimizer sorts the result rows even though they are accessed in the right
order using the selected indexes. The following two situations are in the pitfall
list for DB2 for z/OS V7:

1. A cursor with UNION and ORDER BY always results in a sort, no matter
how good the indexes.
2. A join SELECT with ORDER BY results in a sort if the ORDER BY
refers to a column in a table that is not the outermost table (the table the
optimizer chooses as the first table to access).

Some respected database gurus recommend leaving out the ORDER BY in
these cases, if the result rows are in the right order even without ORDER BY.
They are then assuming that the DBA will always notice any change of access
path. The safest way to eliminate the unnecessary sort is to split the cursor,
which may be time consuming or, if using a purchased package, impossible. A