Queries containing a very high number of JOINs may fail
Details
| Detail name | Value |
|---|---|
| Changelog Number | 11113 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0 |
| Fix Versions | Exasol 7.1.5, Exasol 7.0.15 |
| Resolution Date | 2022-01-26 |
Description
The join order optimizer determines the order of joins in a query. The complexity of the underlying search problem correlates with the number of joins that a query has. Besides the time required to find a plan, the current join order algorithm may also require a lot of heap memory to do so.
For a very high number of joins in a query (more than a hundred joins) the standard heap memory limit of a session may be reached and the query aborted with an error. The following error message indicates this issue:
Connection lost after session running out of memory
Workaround
We recommend to rewrite these types of queries and split them into multiple materialized sub-selects combined together. You may need to force materialization using ORDER BY FALSE to avoid sub-query elimination. If this is not possible to re-write the query or you need additional assistance, please contact Exasol Support for other potential workarounds.
Fix
These types of queries do not use such a high amount of process memory, and run without an error.
