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.0.0
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:
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
