db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: Start with Derby and Full outer JOIN
Date Fri, 04 Mar 2011 14:47:21 GMT
>     * Let A and B are the given tables for Full outer JOIN.
>     * Perform Left outer JOIN using current optimization methods.

I believe that for LEFT and RIGHT joins, the nested loop strategy
is generally a good one, as it is fairly straightforward to arrange
for the outer table to also be the outer loop in the nested loop,
and then it is not hard to arrange to retain those tuples from
the outer table which have no match in the inner table.

For a FULL OUTER join, I wonder if a sort-merge-join strategy
would be a better choice. We could:

- sort the two tables on the join key(s)
- process the two tables in order by join key(s)
- retain all the results, even if there is no match in the merge
   with the other table, filling in null values for the missing tuple

As a side effect, the Derby optimizer might choose to use
sort-merge-join strategies in other situations as well (I think
that currently it uses either nested loops or hash joins in all cases).

thanks,

bryan

Mime
View raw message