Better index selection for UNION ALL indices
Details
| Detail name | Value |
|---|---|
| Changelog Number | 27507 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | Exasol 2025.1.5, Exasol 2025.2.0 |
| Resolution Date | 2025-12-05 |
Background
Whenever a index is needed on a UNION ALL between several tables (e.g. SELECT * FROM T1 UNION ALL SELECT * FROM T2), the system tries to select a matching index from table T1. If none is found, one is created and all other tables in the UNION ALL list are queried for indices matching the one for T1. If no matching indices are found, then indices are created or rebuilt, resulting in unnecessarily long runtimes in case the table T1 is small and the other are very large.
Improvement
The improvement consists in selecting an index such that the total cost of index creation and rebuilding is minimized.
Note
This improvement is enabled by default from Exasol 2025.1.5 and Exasol 2025.2.0, but is disabled for Exasol 8.29.x systems. It can be enabled starting with Exasol 8.29.13 using the following command-line parameter: useLegacyUnionAllIndices=0.
