More optimizer flexibility for execution of OUTER JOINs
Details
| Detail name | Value |
|---|---|
| Changelog Number | 7649 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | Exasol 6.2.0 |
| Resolution Date | 2019-04-10 |
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.
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.
