Optimization to push LIMIT and ORDER BY into UNION ALL
Details
| Detail name | Value |
|---|---|
| Changelog Number | 28742 |
| Type | New Feature |
| Status | Resolved |
| Fix Versions | Exasol 2026.1.0, Exasol 2025.2.1 |
| Resolution Date | 2026-03-20 |
Background
If a simple select has a LIMIT and its FROM clause is a UNION ALL, then it’s beneficial to push LIMIT into UNION ALL. In this case not entire UNION ALL would be materialized, but just number of rows actually needed.
Example
Original query:
SELECT * FROM (SELECT * FROM T1 WHERE C1 > 0 UNION ALL SELECT * FROM T2) LIMIT 5;
is transformed into:
SELECT * FROM (SELECT * FROM T1 WHERE C1 > 0 UNION ALL SELECT * FROM T2 LIMIT 5);
Improvement
Compiler performs optimization when this transformation is valid. If select also has an ORDER BY, it is pushed into UNION ALL as well.
