db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean T. Anderson (JIRA)" <derby-...@db.apache.org>
Subject [jira] Reopened: (DERBY-1601) Document (in Tuning Guide) new subquery materialization via hash join behavior that was introduced with DERBY-781
Date Tue, 19 Sep 2006 17:19:23 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1601?page=all ]

Jean T. Anderson reopened DERBY-1601:
-------------------------------------

             
Reopening to change fix version from 10.2.2.0 to 10.2.1.0. Jira will get updated with the
right version number for the release version of 10.2.1.x.


> 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.2.1.0, 10.3.0.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

        

Mime
View raw message