Without a transformation, a statement that contains both ORDER BY and UNION
would require two separate sorting steps-one to satisfy ORDER BY and
one to satisfy UNION (Currently Derby uses sorting to eliminate duplicates
from a UNION. You can use UNION ALL to avoid sorting, but UNION ALL will return duplicates. So you only use UNION ALL to avoid sorting if you know that there are no duplicate rows in the tables).
In some situations,
Derby can transform the statement internally
into one that contains only one of these keywords (the ORDER BY is thrown
out). The requirements are:
- The columns in the ORDER BY list must be a subset of the columns in the
select list of the left side of the union.
- All the columns in the ORDER BY list must be sorted in ascending order
and they must be an in-order prefix of the columns in the target list of the
left side of the UNION.
Derby will be able to transform the following statements:
SELECT miles, meal
FROM Flights
UNION VALUES (1000, 'D')
ORDER BY 1
Derby cannot avoid two sorting nodes in the following statement,
because of the order of the columns in the ORDER BY clause:
SELECT flight_id, segment_number FROM Flights
UNION
SELECT flight_id, segment_number FROM FlightAvailability
ORDER BY segment_number , flight_id