Database Administrator Guide > Database Administrator Guide > Using the Query Optimizer > Greedy Optimization > Control Greedy Optimization
Was this helpful?
Control Greedy Optimization
By default, greedy optimization is used if the query meets the following two criteria:
1. The number of base tables is at least 5.
2. The combination of base table and potentially useful secondary indexes is at least 10.
For example:
Query
Greedy Used By Default?
7 tables and no indexes
No
3 tables and 7 indexes
No
7 tables and 3 indexes
Yes
The greedy heuristic typically chooses very good query plans, especially when considering the vastly reduced compile time. However, for the rare cases in which greedy optimization produces a much slower plan, it can be turned off.
To control whether greedy optimization is used
To turn greedy optimization off for the session, issue the following SQL statement (for example, from a query tab in Director, from a terminal monitor, from the SQL Scratchpad window in VDBA, or from within an embedded SQL application):
[exec sql] set joinop nogreedy
To turn greedy optimization back on, issue the corresponding statement:
[exec sql] set joinop greedy
To turn off greedy optimization for the installation, set the opf_new_enum configuration parameter (in CBF or VCBF) to OFF.
Last modified date: 08/28/2024