Add query prefix to switch optimizers

Detail name	Value
Changelog Number	27990
Type	New Feature
Status	Resolved
Fix Versions	Exasol 2025.2.0, Exasol 2025.1.9
Resolution Date	2025-12-17

Background

Finding the optimal join order is in general a NP-hard problem.
This means that there is no known polynomial algorithm that can solve the problem.
As a consequence, databases, including Exasol, need to use heuristics and algorithms that find a locally optimal join order. This is not necessary the globally optimal join order.
Currently, Exasol has two different optimizers available. The legacy optimizer and the new standard optimizer.
As both only identify locally optimal solutions, the solutions they find may differ.
In some cases the legacy optimizer finds a join queue that leads to a faster execution time compared to the standard optimizer.

New Feature

To allow users to switch between optimizers on a query by query basis, we introduce two new query prefixes:

/*join optimizer standard*/
/*join optimizer legacy*/

This enforces an optimizer for a single query that contains the prefix. Note that the prefix has to match exact as it is written.
No additional spaces are allowed. It is possible to combine the prefix with “snapshot execution”:

/*snapshot execution*//*join optimizer standard*/
/*snapshot execution*//*join optimizer legacy*/
/*join optimizer standard*//*snapshot execution*/
/*join optimizer legacy*//*snapshot execution*/

Example:

CREATE SCHEMA test;
CREATE TABLE test.t1(a int);
INSERT INTO test.t1 VALUES (1),(2),(3);
CREATE TABLE test.t2(a int);
INSERT INTO test.t2 VALUES (2),(2),(3),(4);

-- Example 1: Enforce the legacy optimizer
-- This enforces the legacy optimizer for this query, even if the standard optimizer is active which is the default setting:
/*join optimizer legacy*/SELECT * FROM test.t1 t1 join test.t2 t2 on t1.a = t2.a;

-- Example 2: Enforce the standard optimizer
CONTROL SET JOIN OPTIMIZER LEGACY;
-- This enforces the standard optimizer for this query, even after the CONTROL statement enabled the legacy optimizer:
/*join optimizer standard*/SELECT * FROM test.t1 t1 join test.t2 t2 on t1.a = t2.a;