[EXASOL2468] MORE OPTIMIZER FLEXIBILITY FOR EXECUTION OF OUTER JOINS

[EXASOL-2468] More optimizer flexibility for execution of OUTER JOINs Created: 15.04.2019  Updated: 17.03.2021  Resolved: 21.05.2019

Status:

Resolved

Project:

EXASOL Roadmap

Component/s:

None

Fix Version/s:

Exasol 6.2.0

Type:

Improvement

Priority:

Normal

Reporter:

Captain EXASOL

Assignee:

Captain EXASOL

Resolution:

Fixed

Votes:

0

Labels:

None

Issue Links:

Changed Behavior:

The additional freedom for the optimizer can lead to faster join queues. However, since the cost model and estimations of an optimizer never are perfect, it is also possible that a join queue with worse execution time is chosen.

Improvement

Prior to this improvement an OUTER JOIN causes a lot of dependencies on the table join order of queries containing them. This was true especially for SQL92 OUTER JOINs, but in less detail also for legacy Oracle outer joins with (+)-syntax. Those restrictions are reduced a lot which allows creation of faster join queues.

Examples

Outer joins can be executed in different order if they are independent of each other:

FROM t1 LEFT JOIN t2 ON t1.x=t2.x  LEFT JOIN t3 on t1.y=t3.y

can now be executed in order t1->t2->t3 (old), but also (new) t1->t3->t2.

Further, also INNER JOINs with direct or indirect link to outer tables now have additional flexibility:

FROM t1 LEFT JOIN t2 ON t1.x=t2.x INNER JOIN t3 on t1.y=t3.y AND t2.z=t3.z

can now be executed in order t1->t2->t3 (old), but also (new) t2->t1->t3 and t1->t3->t2.

Side effects

The additional freedom for the optimizer can lead to faster join queues. However, since the cost model and estimations of an optimizer never are perfect, it is also possible that a join queue with worse execution time is chosen.