Extension of Push Joins into UNION ALL optimization

Detail name	Value
Changelog Number	27602
Type	Improvement
Status	Resolved
Fix Versions	Exasol 2026.1.0
Resolution Date	2026-05-15

Background

Exasol introduced an optimization, that applies GROUP BY on UNION ALL branches, potentially significantly reducing TEMP_DB_RAM. This is a powerful optimization, but it is useless if a join is present in the FROM clause. This feature extends an already existing optimization that pushes tables into UNION ALL branches, and aims to enable pushing GROUP BY into UNION ALL in cases when this might cause a lot of benefit.

Example

select sum(A1), B1, C1 from 
  (select * from A union all select * from A_) 
  inner join B on A1 = B1 
  left join C on B1 = C1 
group by B1, C1;

====> transforms to

select sum(A1), B1, C1 from 
  (select * from 
    (select * from A)
    inner join B on A1 = B1 
    left join C on B1 = C1
    
  union all 
  
  select * from 
    (select * from A_)
    inner join B on A1 = B1 
    left join C on B1 = C1)  
group by B1, C1;

In this example, the optimizer pushes tables B and C into UNION ALL. This enables a subsequent optimization, which pushes GROUP BY there as well.

Improvement

The optimizer can now decide to apply this transformation if it determines that the transformed query, with subsequent GROUP BY pushdown, will execute faster than the original query.