db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Synge" <james.sy...@gmail.com>
Subject HashJoinStrategy.feasible
Date Wed, 11 Oct 2006 14:27:19 GMT
Army mentioned earlier the analysis he wrote for Derby-781 (Subquery
materialization via
Hash Join).  I've read most of that, and am confused by his analysis
of this select:

Q2. select 1 from tt, (t left outer join (s left outer join r on (f =
i)) on (b = e)) where (j=g)

Judging from the analysis, this query could be less ambiguously expressed as:

select 1
    tt as T0,
    (t as T1 left outer join
     (s as T2 left outer join r as T3 on (T2.f = T3.i))
     on (T1.b = T2.e))
where (T0.j=T3.g)

In particular, I don't understand the claim that the subqueries can
not be materialized
because of the fact that the (j=g)  and (b=e) predicates create a
correlation between
the levels.  I can't see any theoretical reason why the following
table expression
couldn't be materialized:

    s as T2 left outer join r as T3 on (T2.f = T3.i)

There is no correlation between this table expression and the rest of
the from clause.
Furthermore, there is no correlation between:

    (t as T1 left outer join (s as T2 left outer join r as T3 on (T2.f
= T3.i)) on (T1.b = T2.e))

And the rest of the from clause (just TT in this case).

My understanding of the semantics of select are that (logically) the
FROM clause is
executed first to produce a working table (in this case a CROSS JOIN
of TT with the
result of the left outer joins, which are evaluated independently of TT).

Army (or anyone else), can you provide a more detailed explanation of this?


View raw message