optimization_goal option

Determines whether query processing is optimized towards returning the first row quickly, or minimizing the cost of returning the complete result set.

Allowed values

First-row, All-rows

Default

All-rows

Scope

	PUBLIC role	For current user	For other users
Allowed to set permanently?	Yes, with SET ANY PUBLIC OPTION	Yes	Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily?	Yes, with SET ANY PUBLIC OPTION	Yes (current connection only)	No

Remarks

The optimization_goal option controls whether SQL data manipulation language (DML) statements are optimized for response time or total resource consumption.

If the option is set to All-rows (the default), then a query is optimized to choose an access plan with the minimal estimated total retrieval time. Setting optimization_goal to All-rows may be appropriate for applications that intend to process the entire result set, such as SAP PowerBuilder DataWindow applications. A setting of All-rows is also appropriate for insensitive (ODBC static) cursors since the entire result is materialized when the cursor is opened. It may also be appropriate for scroll (ODBC keyset-driven) cursors, since the intent of such a cursor is to permit scrolling through the result set.

If the option is set to First-row, the optimizer chooses an access plan that is intended to reduce the time to fetch the first row of the query's result, possibly at the expense of total retrieval time. In particular, the optimizer typically avoids, if possible, access plans that require the materialization of results to reduce the time to return the first row. With this setting, the optimizer favors access plans that use an index to satisfy a query's ORDER BY clause, rather than plans that require an explicit sorting operation.

You can use the FASTFIRSTROW table hint in a query's FROM clause to set the optimization goal for a specific query to First-row, without having to change the optimization_goal setting.

You can override any temporary or PUBLIC settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements by including an OPTION clause in the statement.