The following options affect query processing speed:
AGGREGATION_PREFERENCE – Controls the choice of algorithms for processing an aggregate (GROUP BY, DISTINCT, SET functions). This option is designed primarily for internal use; do not use it unless you are an experienced database administrator.
DEFAULT_HAVING_SELECTIVITY_PPM – Sets the selectivity for all HAVING predicates in a query, overriding optimizer estimates for the number of rows that will be filtered by the HAVING clause.
DEFAULT_LIKE_MATCH_SELECTIVITY_PPM – Sets
the default selectivity for generic LIKE predicates, for example, LIKE 'string%string' where % is
a wildcard character. The optimizer relies on this option when other
selectivity information is not available and the match string does
not start with a set of constant characters followed by a single
wildcard.
DEFAULT_LIKE_RANGE_SELECTIVITY_PPM – Sets
the default selectivity for leading constant LIKE predicates, of
the form LIKE 'string%' where
the match string is a set of constant characters followed by a single
wildcard character (%). The optimizer relies on this option
when other selectivity information is not available.
EARLY_PREDICATE_EXECUTION – Controls whether simple local predicates are executed before join optimization. Under most circumstances, it should not be changed.
IN_SUBQUERY_PREFERENCE – Controls the choice of algorithms for processing IN subqueries. This option is designed primarily for internal use; do not use it unless you are an experienced database administrator.
INDEX_PREFERENCE – Sets the index to use for query processing. The Sybase IQ optimizer normally chooses the best index available to process local WHERE clause predicates and other operations which can be done within an IQ index. This option is used to override the optimizer choice for testing purposes; under most circumstances it should not be changed.
JOIN_PREFERENCE – Controls the choice of algorithms when processing joins. This option is designed primarily for internal use; do not use it unless you are an experienced database administrator.
JOIN_SIMPLIFICATION_THRESHOLD – Controls the minimum number of tables being joined together before any join optimizer simplifications are applied. Normally you should not need to change this value.
MAX_HASH_ROWS – Sets the maximum estimated number of rows the query optimizer will consider for a hash algorithm. The default is 1,250,000 rows. For example, if there is a join between two tables, and the estimated number of rows entering the join from both tables exceeds this option value, the optimizer will not consider a hash join. On systems with more than 50MB per user of TEMP_CACHE_MEMORY_MB, you may want to consider a higher value for this option.
MAX_JOIN_ENUMERATION – Sets the maximum number of tables to be optimized for join order after optimizer simplifications have been applied. Normally you should not need to set this option.
