Improve join optimizer robustness to statistical outliers

Detail name	Value
Changelog Number	4268
Type	Improvement
Status	Resolved
Fix Versions	EXASolution 5.0.18, EXASOL 6.0.1
Resolution Date	2017-03-16

In some cases, the optimization of a join is influenced by statistical outliers.

For instance, in the query

CREATE SCHEMA S;

CREATE OR REPLACE TABLE T1(A1 INT, B1 INT);
INSERT INTO T1 VALUES (1,1),(2,2),(3,3),(4,4);

CREATE OR REPLACE TABLE T2(A2 INT, B2 INT);
INSERT INTO T2 VALUES (1,1),(2,2),(3,3),(4,4),(100,5);

SELECT * FROM T1 JOIN T2 ON A1=A2;

the value 100 of T2.A2 is an outlier and leads to a non-optimal estimation of the optimizer.

Starting with R5.0.18 the command line parameter

-enableJoinSelectivityEstimation=0

can be set to make the internal estimation of the joins more robust to outliers.

Starting with R6.0.1 this improved join estimation is enabled by default, i.e., the command line parameter is set to 0 by default.