Activation of new Join Order Optimizer by default

Detail name	Value
Changelog Number	12517
Type	Improvement
Status	Resolved
Fix Versions	Exasol 8.0.0
Resolution Date	2022-04-20

Background

Join order optimization is a complex problem and an active area of research. While most Exasol users do not experience any problems with join orderings in their productive workloads, we evaluate alternative approaches and make the most promising ones available to all Exasol users. In 7.0 and 7.1 Exasol introduced the new join order optimizer (see EXASOL-2635) as a preview feature.

New Feature

The new join order optimizer is now activated by default.

The new join order optimizer inspects more options for joins in order to find the optimal join order and is faster than the previous optimizer.  The new optimizer offers several benefits, such as:

• Faster join order optimization (i.e. faster compile time) for complex queries and occasionally faster query execution through improved join queues.
• Future-proof since some improvements to join order optimization will only be implemented in the new join order optimization algorithm.

Additional Details

With this change the old optimizer is in maintenance mode only. It will not get new features and might get deprecated in a future release. However, we plan to keep the old optimizer as an option as long as there are still customers that need it.

Some queries may not perform as fast as with the old optimizer. In these cases, you can contact support to improve the new optimizer or use the old optimizer (as described below). 

The old optimizer can be activated on a session or database-wide setting:

• Session-wide using the following SQL

-- set to old optimizer (legacy)
CONTROL SET JOIN OPTIMIZER LEGACY;

-- set to new optimizer (standard)
CONTROL SET JOIN OPTIMIZER STANDARD;

-- switches to database default (new optimizer)
CONTROL CLEAR JOIN OPTIMIZER;

• Database-wide using the command-line parameter -joinOrderMethod=0 (requires database restart)

Changed Behavior

 

Due to the switch to the new standard join order optimizer, the previous session configuration options for CONTROL SET JOIN OPTIMIZER were renamed to reflect that change.

The following syntax is no longer valid:

CONTROL SET JOIN OPTIMIZER DEFAULT;
CONTROL SET JOIN OPTIMIZER EXPERIMENTAL;

The new syntax is:

-- what was 'DEFAULT' is now 'LEGACY'
CONTROL SET JOIN OPTIMIZER LEGACY;
-- what was 'EXPERIMENTAL' is now 'STANDARD'
CONTROL SET JOIN OPTIMIZER STANDARD;

 

Changed behavior

1. *The new join order optimizer is now default* 2. Due to the switch to the new standard join order optimizer the previous session configuration options for {{CONTROL SET JOIN OPTIMIZER}} were renamed to reflect that change. The old names are no longer usable: * {{DEFAULT}} is now {{LEGACY}} * {{EXPERIMENTAL}} is now {{STANDARD}} So the following statements will need adaptions in case you used them: {code:sql} CONTROL SET JOIN OPTIMIZER DEFAULT; CONTROL SET JOIN OPTIMIZER EXPERIMENTAL; {code} Also keep in mind that the changed default will invalidate the reason for some of those commands where they only set to the new standard. We recommend to try without those statements and rely on the improved new optimizer, instead. If you want to keep those statements the new syntax is: {code:sql} -- what was 'DEFAULT' is now 'LEGACY' CONTROL SET JOIN OPTIMIZER LEGACY; -- what was 'EXPERIMENTAL' is now 'STANDARD' CONTROL SET JOIN OPTIMIZER STANDARD; {code}