Improve index creation by MERGE statement for large target tables

Detail name	Value
Changelog Number	5545
Type	Improvement
Status	Resolved
Fix Versions	Exasol 6.0.14, Exasol 6.1.1
Resolution Date	2018-11-27

Problem Description

In 6.0.0 we introduced MERGE enhancements for large target tables (see EXASOL-1788).

However, the new MERGE logic has the drawback that it creates indexes on the target table to execute the most efficient join from the source-table to the target-table. Depending on size and distribution keys of the source-table and target-table, over time an established merge-process might add both a LOCAL and a GLOBAL index on the target table.

In some cases those indexes alone will take a significant part of the DBRAM and might produce index maintenance cost if not used.

Improvement

We will treat smaller and larger source tables identically when merging into large target tables. Smaller source tables no longer use a local merge unless the same source table with more data could do a local merge as well. This way only a single index type (LOCAL or GLOBAL) is created by several MERGE-statements with same target table and structurally identical source tables.

For scenarios without large target tables nothing is changed.

Changed behavior

Some MERGE-statements with small source tables could be slower. The behaviour can be switched back to previous logic if that is a problem.