Controls whether key joins are permitted. WITH KEYJ permits keyjoins, although it may not result in a key join being compiled into a query plan. WITH NOKEYJ assures that no key joins are used.

Default: WITH KEYJ

Controls whether query flattening is used to optimize queries, including queries involving aggregate subselects or singleton subselects.

Default: WITH FLATTEN

Controls whether the query optimizer uses the transformation that converts a NOT EXISTS/NOT IN subselect to an outer join with the containing query.

Default: WITH OJFLATTEN

Specifies whether to display a diagrammatic representation of the query execution plan chosen for the query by the optimizer.

Default: WITH NOQEP

Enables or disables the exhaustive enumeration heuristic of the query optimizer for complex queries.

When the query references a large number of tables, the greedy enumeration heuristic enables the optimizer to produce a query plan much faster than with its default technique of exhaustive searching for query execution plans. For details on the greedy optimization heuristic, see the Database Administrator Guide.

Specifies whether the query optimizer should consider using hash aggregation and hash joins.

Default: WITH HASH

Specifies whether the query optimizer should consider using hash aggregation (see [No]Hashagg).

Default: WITH HASHAGG

Specifies whether the query optimizer should consider using hash joins (see [No]Hashjoin).

Default: WITH HASHJOIN

Controls whether the query optimizer enables the generation of parallel query execution plans.

For a discussion of parallel query plans, see the Database Administrator Guide.

Default: WITH NOPARALLEL

Sets the parallelism level for the query, where n is an integer from 1 to 256.

The value of n should not exceed the number of CPU cores visible to the operating system. Your query may run at a lower level of parallelism than requested if other parallel queries are running concurrently.