One object of optimization is to save storage space, i.e., to avoid the generation of result tables. Apart from the cases in which the element FOR REUSE or an existing join enforces the creation of result tables, building a result table is avoided as far as possible.
With an ORDER BY specification, it is only possible to do without a result table under the following conditions:
- There is no strategy better than the sequential search,
and
- neither <distinct spec> nor FOR REUSE is specified,
and
- there is a single-column indexed column according to which a sort is performed,
or
- there are several columns according to which a sort has to be performed which, in a specified sequence and order (ascending or descending), form a named index.
The EXPLAIN output indicates whether the result table is generated (RESULT IS COPIED) or not (RESULT IS NOT COPIED).