Subquery Cache Within Single Query
Details
| Detail name | Value |
|---|---|
| Changelog Number | 23792 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | Exasol 2025.1.3, Exasol 2025.2.0 |
| Resolution Date | 2025-09-17 |
Background
Some customers face high execution times and resource utilization due to the repeated execution of the same subqueries. The compiler often generates duplicate subqueries from views/CTEs. This can cause independent materializations of the duplicates. In combination with virtual schemas, this can create queries that execute the same IMPORT over and over again which is a particularly expensive operation since it involves a remote database.
Improvement
The optimizer can now detect identical subqueries (including IMPORT statements) and materialize them only once.
Examples
-- Example #1: IMPORT is executed only once.
select * from (import into (X int) from JDBC at MY_CONNECTION table REMOTE.T)
cross join
(import into (X int) from JDBC at MY_CONNECTION table REMOTE.T);
-- Example #2: second operand is not executed. It is replaced with a materialized table of the first operand.
select * from T1, T2, T3, T4, T5
UNION ALL
select * from T1, T2, T3, T4, T5;
Limitations
In some cases identical subqueries are not cached. For example, Exasol does not cache subqueries that contain UDFs. This is necessary because there is no guarantee that UDF returns the same result for each invocation.
