Sorry - I somehow deleted part of my last message before sending it.
I'll try again:
The actual query that prompted this question, though, has a subquery
that uses aggregates and a GROUP BY--i.e. the subquery *cannot*, as
I understand it, be flattened into the outer query, because the
aggregate/group-by functionality has to be performed before
evaluation of the outer query can occur. Ex.
select t1.i, x1.s1 from t1 inner join (select distinct j, sum(b) s1
from t2 group by j) x1 on x1.j = t1.i;
OK, I see. There are ways to flatten some types of aggregate
subqueries, but they are complicated and I wouldn't want to get into
it. I agree that in this case it would be worth investigating a hash
join with the subquery.
Unfortunately, I don't remember why hash joins are prohibited in this
case. One thing I notice in looking at the code is the following in
HashJoinStrategy.feasible():
/* Look for equijoins in the predicate list */
hashKeyColumns = findHashKeyColumns(
innerTable,
cd,
predList);
I don't know what the predicate list looks like at this point for the
query in question. Perhaps there's something about it that makes it
hard to find equijoins that should make up the hash key columns.
The only thing I can suggest is to try removing the restriction and
see what happens. I'll try to help if you run into problems.
- Jeff Lichtman
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
