Transformation to inner join not performed for certain three-way joins
----------------------------------------------------------------------
Key: DERBY-4405
URL: https://issues.apache.org/jira/browse/DERBY-4405
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.6.0.0
Reporter: Knut Anders Hatlen
Priority: Minor
In the CROSS JOIN section in the reference manual
(http://db.apache.org/derby/docs/dev/ref/rrefsqljcrossjoin.html) there are
three examples that are supposed to be equivalent. However, the performance
differs significantly between the different queries.
The queries use the tours db and look like this:
(1)
SELECT * FROM CITIES LEFT OUTER JOIN
(FLIGHTS CROSS JOIN COUNTRIES)
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'
(2)
SELECT * FROM CITIES LEFT OUTER JOIN
FLIGHTS INNER JOIN COUNTRIES ON 1=1
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'
(3)
SELECT * FROM CITIES LEFT OUTER JOIN
(SELECT * FROM FLIGHTS, COUNTRIES) S
ON CITIES.AIRPORT = S.ORIG_AIRPORT
WHERE S.COUNTRY_ISO_CODE = 'US'
When executed in ij, (1) and (2) need 6 seconds to complete, whereas (3)
completes in 50 ms.
The query plans for (1) and (2) use nested loop joins and table scans. (3) uses
a combination of hash join and nested loop join, and index scans as well as
table scans.
It looks like (3) has been rewritten from a left outer join to an inner join
internally. This is fine because all rows that have the right-side columns
filled with NULLs will be filtered out by the predicate
S.COUNTRY_ISO_CODE='US', so the extra rows generated by the outer join will not
be returned.
This optimization should also be possible for (1) and (2). We should improve
the logic so that those joins are transformed too. The transformation happens
in HalfOuterJoinNode.transformOuterJoins().
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
