db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1601) Document (in Tuning Guide) new subquery materialization via hash join behavior that was introduced with DERBY-781
Date Thu, 27 Jul 2006 19:11:14 GMT
Document (in Tuning Guide) new subquery materialization via hash join behavior that was introduced
with DERBY-781

                 Key: DERBY-1601
                 URL: http://issues.apache.org/jira/browse/DERBY-1601
             Project: Derby
          Issue Type: Improvement
          Components: Documentation
    Affects Versions:
            Reporter: A B
            Priority: Minor

I did a quick glance through the Tuning Guide and noticed that there is a section about subquery
materialization.  Given the changes for DERBY-781, I think it might be good to update the
documentation to account for the behavior introduced by DERBY-781.

I think the following information should be added to the end of the section entitled "Internal
language transformations" --> "Subquery processing and transformations" --> "Materialization"
of the Tuning Guide.

<begin new documentation>

Materialization of a subquery can also occur when the subquery is non-flattenable and there
is an equijoin between the subquery and another FROM table in the query.  For example:

WHERE t1.i = x1.a AND t1.i in (1, 3, 5, 7)

In this case the subquery x1 is non-correlated because it doesn't reference any columns from
the outer query, and it is non-flattenable because of the DISTINCT keyword (Derby doesn't
flatten DISTINCT subqueries).  Thus this subquery is eligible for materialization.

Then, since there is an equijoin predicate between the subquery x1 and the table t1 (namely,
t1.i = x1.a), the Derby optimizer will consider doing a hash join between t1 and x1 (with
x1 as the inner operand).  If that yields the best cost, Derby will materialize the subquery
x1 in order to perform the hash join.  This means that the subquery will only be evaluated
a single time and the results will be stored into an in-memory hash table.  Derby can then
execute the join using the in-memory result set for x1.

<end new documentation>

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message