db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@sbcglobal.net>
Subject Re: VTI, Indexed Lookup and the Query Optimizer
Date Mon, 16 Jan 2006 23:27:43 GMT
Jeffrey Lichtman wrote:
> 
>> That said, it seems reasonable to think that the optimizer should at 
>> least consider doing a hash join on the subquery, in which case the 
>> join between T2 and T3 could be materialized and then a hash-join 
>> could be done using the predicate x1.j = t1.i.
> 
> I think the best thing would be to flatten the subquery into the outer 
> query so that the optimizer will see it as just another join.

Based on logic in the code, the example query isn't flattenable.  In 
FromSubquery.preprocess(), there is a check to see if the subquery is flattenable:

/* Return if the FSqry is flattenable()
<snip>
*/
if ((gbl == null || gbl.size() == 0) &&
     tableProperties == null &&
     subquery.flattenableInFromSubquery(fromList))
{
     ...
}

"subquery" in the above code is a SelectNode, and the 
flattenableInFromSubquery() method of SelectNode has the following:

...
if (this.fromList.size() > 1)
{
	return false;
}
...

So the example subquery, which has two tables, isn't flattenable.  Note that 
even if it _was_ flattenable, the following code in TableOperatorNode.java will 
still ultimately call extractSubquery:

leftResultSet = leftResultSet.preprocess(numTables, gbl, fromList);
/* If leftResultSet is a FromSubquery, then we must explicitly extract
  * out the subquery (flatten it).  (SelectNodes have their own
  * method of flattening them.
  */
if (leftResultSet instanceof FromSubquery)
{
	leftResultSet = <leftResultSet.extractSubquery(...)>
}

rightResultSet = rightResultSet.preprocess(numTables, gbl, fromList);
/* If rightResultSet is a FromSubquery, then we must explicitly extract
  * out the subquery (flatten it).  (SelectNodes have their own
  * method of flattening them.
  */
if (rightResultSet instanceof FromSubquery)
{
	rightResultSet = <rightResultSet.extractSubquery(...)>
}

So either way, we replace the FromSubquery w/ a PRN over a SelectNode, and hence 
my earlier question.

Note also that the optimizer _does_ appear to consider this as "just another 
join"--it has two optimizables, a FromBaseTable (T1) and a PRN (over the 
subquery) and it's trying to find the right join order.  What I was wondering is 
why the optimizer doesn't ever consider a hash join in this case; the answer is 
because the child of the PRN, which is a SelectNode, isn't an optimizable (as 
per PRN.isMaterializable()).

That is, of course, just my understanding.  Is there a different area that you 
can think of where flattening should be occuring?  Can you give more info about 
where that flattening needs to be done?

> BTW, in your example the subquery contains a Cartesian product. Is that 
> intentional?

Just an example ;)

Army


Mime
View raw message