Optimization: Push joins through UNION ALL

Detail name	Value
Changelog Number	22914
Type	Improvement
Status	Resolved
Fix Versions	Exasol 8.34.0
Resolution Date	2025-04-16

Background

For some queries that have a join where one of the join tables contains a UNION ALL, an equivalent query is to push the join into and evaluate it for each branch of the UNION ALL instead. The transformed query can run faster than the original, but it can also be slower.

This optimization is not applied to outer joins.

Example

-- Setup
create table U (ID int, C1 int);
create table V (ID int, C1 int);
create table T (C1 int, C2 int);

-- Original query
select *
from (select * from U where U.ID between 1000 and 2000
      union all
      select * from V where V.ID between 3000 and 4000) as UV
     inner join T on UV.C1 = T.C1 and T.C2 = 1;

-- Equivalent transformed query with T pushed through the UNION ALL
select *
from (select * from U inner join T on U.C1 = T.C1 and T.C2 = 1
      where U.ID between 1000 and 2000
      union all
      select * from V inner join T on V.C1 = T.C1 and T.C2 = 1
      where V.ID between 3000 and 4000) as UV;

Improvement

The optimizer can now decide to do this transformation, if it decides the transformed query will run faster than the original query.