[ http://issues.apache.org/jira/browse/DERBY-1601?page=all ]
Laura Stewart updated DERBY-1601: --------------------------------- Derby Info: [Patch Available] > 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