New optimizer: Bad join queue for implicit cross join

Details

Detail name Value
Changelog Number 13253
Type Bug
Status Open
Affected Versions Exasol 7.0.0, Exasol 7.1.0

Description

The new non-default join order optimizer can generate bad join queues for joins having implicit cross joins. This means there is a join condition, but no index can be used for this condition resulting in a cross join followed by a filter.

Preparation

CREATE OR REPLACE TABLE t1 AS SELECT v c1, CAST(MOD(v,4000000) AS VARCHAR(10)) c2 FROM VALUES BETWEEN 1 AND 5000000 v(v) ;
CREATE OR REPLACE TABLE t2 AS SELECT CAST(v AS VARCHAR(10)) c1 FROM VALUES BETWEEN 1 AND 1000 v(v) ;
CREATE OR REPLACE TABLE t3 AS SELECT MOD(v,5000000) c1 FROM VALUES BETWEEN 1 AND 50000000 v(v) ;

Example

SELECT COUNT(*)
FROM t1
JOIN t2 ON UPPER( t1.c2 ) = UPPER( t2.c1 ) OR ( t1.c2 IS NULL AND t2.c1 IS NULL )
JOIN t3 ON t1.c1 = t3.c1;

Workaround

Avoid join conditions where no index can be used like UPPER( t1.c2 ) = UPPER( t2.c1 ) OR ( t1.c2 IS NULL AND t2.c1 IS NULL ). There are alternatives that will speed up this join significantly (up to factor 100):

  1. Rewrite the join condition so that an expression index can be used:
    UPPER( NVL(t1.c2, 'unused value') ) = UPPER( NVL(t2.c1, 'unused value') )
  2. Move the expression UPPER from the join condition to an ETL step before (into a new column). Then the join condition can be rewritten using the fast OR NULL equi joins (EXASOL-2197):
    t1.c2_upper = t2.c1_upper OR ( t1.c2_upper IS NULL AND t2.c1_upper IS NULL )

Fix

Better join queues are created potentially leading to better query performance.
Attention: Best practice is still to avoid such join conditions and use an additional ETL step as described in the second workaround.