db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4405) Transformation to inner join not performed for certain three-way joins
Date Mon, 12 Oct 2009 11:37:31 GMT
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.


Mime
View raw message