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

["A B (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-1601?page=comments#action_12429878 ] 
            
A B commented on DERBY-1601:
----------------------------

I noticed that the diff file contains changes other than what I've proposed for this issue.  While those changes might be good in the long run, I'm not comfortable approving them as part of this issue, which targets the addtion of specific information.

I think the rules of code patches also apply to documentation patches in this sense: namely, a patch should be restricted to whatever changes address the current issue.  If more changes are required, it's best to submit them as separate patches, usually associated with a new Jira entry...

That said, the doc additions that correlate to this particular issue look good to me.  If we can back out the other changes, I'd say it's ready for commit.  At the very least, the following two changes should be backed out:

-- The words "an exception will be thrown at the beginning of execution" were replaced with "an exception is returned when the subquery is run".  I believe the former is more technically correct and that's what should remain in the documentation.

-- The following sentences:

  Subquery materialization is detected prior to optimization, which allows the optimizer to
  see a materialized subquery as an unknown constant value. The comparison is thus
  optimizable.

were replaced with new sentences, but a period is missing between the new sentences.  I think it'd be best to keep the original ones...

Thanks Laura!

> 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