[ http://issues.apache.org/jira/browse/DERBY-1601?page=all ]
Laura Stewart updated DERBY-1601:
---------------------------------
Attachment: derby1601_tuning.diff
ctuntransform25857.html
Updated the Materialization topic to reflect the other type of subquery that
can be materialized.
> 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
> Attachments: ctuntransform25857.html, derby1601_tuning.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
