A goal of optimization is to save memory; that is, the creation of results tables should be avoided. Except for the cases in which the syntax element FOR REUSE or the existence of a join forces the creation of results tables, the creation of a results table is avoided where possible.
A possible search strategy is therefore the postponement of the search to the FETCH time (FETCH statement), without the creation of a results table. In this way, no memory is used for results, quick access to the first results is possible, and a quicker comparison can be made of the received and the desired results.
The postponement of the search to the FETCH time is not possible for all SQL statements. The following is an overview of a number of SQL statements for which a postponement is not permissible:
SELECT
for multiple tables (Join)
SELECT ... FOR REUSE
SELECT DISTINCT ... (in most cases)
SELECT ... ORDER BY ... (in most cases)
If the ORDER clause was specified, it is only possible to avoid the creation of a results table, if all of the following conditions are met:
· Neither a DISTINCT specification (<distinct spec>) (with a few exceptions), nor the syntax element FOR REUSE, are specified.
· The columns by which the system is to sort form an index in the specified order and with the specified sorting (ascending or descending).
You can use the EXPLAIN statement to display whether a results table is created (RESULT IS COPIED) or not (RESULT IS NOT COPIED).