db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: Optimizing subqueries [ Was: Re: VTI, Indexed Lookup and the Query Optimizer ]
Date Wed, 18 Jan 2006 02:43:46 GMT
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 

         /* Look for equijoins in the predicate list */
         hashKeyColumns = findHashKeyColumns(

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
                                 Check out Swazoo Koolak's Web Jukebox at

View raw message