SAP Sybase Blog

Answer — Optimization goals allow you to choose an optimization strategy that best fits your query environment:

• allrows_mix – the default goal, and the most useful goal in a mixed-query environment. allows_mix balances the needs of OLTP and DSS query environments.
• allrows_dss – the most useful goal for operational DSS queries of medium to high complexity. Currently, this goal is provided on an experimental basis.
• allrows_oltp – the optimizer considers only nested-loop joins.

At the server level, use sp_configure. For example:sp_configure “o

ptimization goal”, 0, “allrows_mix”
At the session level, use set plan optgoal. For example:

set plan optgoal allrows_dss

At the query level, use a select or other DML command. For example:
select * from A order by A.a plan ”(use optgoal allrows_dss)”
In general, you can set query-level optimization goals using select, update, and delete statements. However, you cannot set query-level optimization goals in pure insert statements, although you can set optimization goals in insert…select
statements.

Courtesy: Sybooks