GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/trafodion/pull/1530
[TRAFODION-3031] Fix two issues with nested subquery plans
See the JIRA for a discussion of the problem.
This set of changes contains two fixes:
1. The heuristics that decide whether to do the semi-join to join +
group-by have been modified. First, if the group by would reduce the number of
rows in the inner table by a factor of 5.0 or more, the transformation will be
enabled. The rationale is that this may reduce the size of the inner hash table
if hash join is chosen. Also it allows the join to be commuted, which may lead
to a better plan. Second, if the inner table has only a small number of rows
(less than 100.0), the transformation will be enabled. The rationale here is
that it allows the join to be commuted which may lead to an efficient nested
join plan. Both of the constants here are controlled by new CQDs. (Note: Thanks
go to @sureshsubbiah for the first of these ideas and the code to do it.)
2. There was a bug in Scan::addIndexInfo, where we would sometimes overlook
a useful index. We would mistakenly think that another index provided the same
or better information when in fact one index would satisfy a join predicate
that the other would not. This bug has been fixed.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/trafodion NestedSubqueryProblem
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1530.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1530
----
commit ee91b337ae1d23ad2b15034602e06c4d3bf1103d
Author: Dave Birdsall <dbirdsall@...>
Date: 2018-04-19T22:59:16Z
[TRAFODION-3031] Fix two issues with nested subquery plans
----
---
