Re: Optimizing subqueries [ Was: Re: VTI, Indexed Lookup and the Query Optimizer ]

[Jeffrey Lichtman <sw...@rcn.com>]

>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():

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
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/