Join result caching for large replicated tables
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5094 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | Exasol 6.1.0 |
| Resolution Date | 2018-09-21 |
Join result caching is an optimization technique to speed up joins in which the same keys are joined many times. The final matching rows for each key are stored in a dictionary and can be retrieved very fast afterwards.
There is a caching mechanism for global joins with large tables as well as local joins with very small tables. The performance gain can vary significantly but often a considerable amount in network traffic and cpu time can be saved, especially when the rows returned by the index are further reduced by filter expressions on the adjoined table.
Problem
In case of medium sized tables that have more than 100.000 rows but are still replicated in joins (increased replication border), there is no join result caching applied. As a result the local join performance does not scale well with the table sizes as opposed to global joins.
Improvement
Caching mechanism will be also applied to medium sized tables that are joined locally if the optimizer presumes that caching will improve the performance of this particular query.
Workaround
Set replication border to standard value (100.000 rows).
