8. SQL Statements : SELECT (interactive) : Select Statement Clauses : WITH Clause for SELECT
 
Share this page                  
WITH Clause for SELECT
The WITH clause on the SELECT statement consists of a comma-separated list of one or more of the following options:
[NO]KEYJ
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
[NO]FLATTEN
Controls whether query flattening is used to optimize queries, including queries involving aggregate subselects or singleton subselects.
Default: WITH FLATTEN
[NO]OJFLATTEN
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
[NO]QEP
Specifies whether to display a diagrammatic representation of the query execution plan chosen for the query by the optimizer.
Default: WITH NOQEP
[NO]GREEDY
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.
[NO]HASH
Specifies whether the query optimizer should consider using hash aggregation and hash joins.
Default: WITH HASH
[NO]HASHAGG
Specifies whether the query optimizer should consider using hash aggregation (see page [No]Hashagg).
Default: WITH HASHAGG
[NO]HASHJOIN
Specifies whether the query optimizer should consider using hash joins (see page [No]Hashjoin).
Default: WITH HASHJOIN
[NO]PARALLEL
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