[jira] Closed: (DERBY-1601) Document (in Tuning Guide) new subquery materialization via hash join behavior that was introduced with DERBY-781

["Laura Stewart (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-1601?page=all ]

Laura Stewart closed DERBY-1601.
--------------------------------


Closing this issue. Rick merged the patch(es) for this issue to the 10.2 branch on 8/25.

> 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: 10.2.1.0
>            Reporter: A B
>         Assigned To: Laura Stewart
>            Priority: Minor
>             Fix For: 10.3.0.0, 10.2.2.0
>
>         Attachments: ctuntransform25857.html, ctuntransform25857.html, derby1601_tuning.diff, derby1601_tuning2.diff
>
>
> 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:
> SELECT i, a
> FROM t1, (SELECT DISTINCT a FROM T2) x1
> 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